日期:2014-05-16  浏览次数:20877 次

MySQL中ROLLUP的替代方法

今天从MySQL中提取数据,使用ROLLUP统计后,想在数据左边列中加入项目名称,使用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)

?

显示为空值,修改如下:

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)

可以显示出来但却失去ROLLUP的简洁,有没有更好的方法?