需求:
一次!一次! 查詢出
24小時內
訂單狀態為成功的訂單總數,
訂單狀態為成功的訂單總金額,
訂單狀態為失敗的訂單總數,
訂單狀態為失敗的訂單總金額,
已訂單型別欄位為條件分行.
引數資料:
order表名: order
表中的欄位:
type(1:淘寶,2:京東)
status(5:成功,7:失敗)
id(主鍵)
paySum(實際支付金額)
題主只成功實作了按狀態分兩次查詢,一次查詢出訂單狀態為成功的訂單總數(當然group by type了)
兩次查詢的sql陳述句我貼出來了,求各位老師、前輩、大帥哥們幫忙:
SELECT
c.type,
COUNT(c.id) AS orderSumNum,
SUM(c.amount_payment) AS orderSaleSumNum
FROM
g_order AS c
WHERE
c.`status` = 5
GROUP BY
type
+------+-------------------+--------------------+
|type |orderSumNum |orderSaleSumNum |
+------+-------------------+--------------------+
|1 |30 |314363.00 |
+------+-------------------+--------------------+
|2 |1 |1999.00 |
+------+-------------------+--------------------+
我想實作的效果是這樣的
+------+-------------------+--------------------++-------------------+---------------------------+
|type |orderSumNum |orderSaleSumNum |orderCancelNum |orderCancelSaleSumNum |
+------+-------------------+--------------------++-------------------+---------------------------+
|1 |30 |314363.00 |10 |363.00 |
+------+-------------------+--------------------++-------------------+---------------------------+
|2 |1 |1999.00 | 0 | 0 |
+------+-------------------+--------------------+---------------------+---------------------------+
已訂單型別來區分,并且在一行中展示該訂單型別狀態為5的訂單總數和金額以及訂單型別為7的訂單總數和金額
uj5u.com熱心網友回復：
MariaDB [test]> select * from t6;
+----+------+--------+--------+
| id | type | status | paySum |
+----+------+--------+--------+
| 1 | 1 | 5 | 300.00 |
| 2 | 2 | 7 | 233.00 |
| 3 | 1 | 7 | 400.00 |
| 4 | 2 | 5 | 200.00 |
| 5 | 1 | 5 | 100.00 |
| 6 | 2 | 5 | 150.00 |
| 7 | 1 | 5 | 90.00 |
+----+------+--------+--------+
7 rows in set (0.00 sec)
MariaDB [test]> SELECT
-> type,
-> COUNT(IF(status=5, NULL, id)) AS orderSumNum,
-> SUM(IF(status=5, NULl, paySum)) AS orderSaleSumNum,
-> COUNT(IF(status=7, NULL, id)) AS orderCancelNum,
-> SUM(IF(status=7, NULl, paySum)) AS orderCancelSumNum
-> FROM t6
-> GROUP BY type;
+------+-------------+-----------------+----------------+-------------------+
| type | orderSumNum | orderSaleSumNum | orderCancelNum | orderCancelSumNum |
+------+-------------+-----------------+----------------+-------------------+
| 1 | 1 | 400.00 | 3 | 490.00 |
| 2 | 1 | 233.00 | 2 | 350.00 |
+------+-------------+-----------------+----------------+-------------------+
2 rows in set (0.00 sec)
uj5u.com熱心網友回復：
大神，能幫我看下嗎？https://bbs.csdn.net/topics/395716462
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/31658.html
標籤：MySQL
上一篇：postgresql不能在同一實體上進行發布與訂閱？
下一篇：mysql