今天從MySQL中提取數據,使用ROLLUP統計後,想在數據左邊列中加入項目名稱,使用SQL
Sql代碼
SELECT (CASE WHEN ISNULL(PLAYERNO) THEN PLAYERNO=TOTAL ELSE PLAYERNO END) AS PLAYERNO,SUM(AMOUNT) FROM penalties
GROUP BY PLAYERNO WITH ROLLUP
結果是:
這樣
+----------+-------------+
| PLAYERNO | SUM(AMOUNT) |
+----------+-------------+
| 6 | 100.00 |
| 8 | 25.00 |
| 27 | 175.00 |
| 44 | 130.00 |
| 104 | 50.00 |
| NULL| 480.00 |
+----------+-------------+
6 rows in set, 1 warning (0.00 sec)
顯示為空值,修改如下:
Sql代碼
SELECT PLAYERNO,SUM(AMOUNT) FROM penalties
GROUP BY PLAYERNO
UNION
SELECT TOTAL,SUM(AMOUNT)
FROM penalties
顯示結果:
+----------+-------------+
| PLAYERNO | SUM(AMOUNT) |
+----------+-------------+
| 6 | 100.00 |
| 8 | 25.00 |
| 27 | 175.00 |
| 44 | 130.00 |
| 104 | 50.00 |
| TOTAL | 480.00 |
+----------+-------------+
6 rows in set (0.00 sec)