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的简洁，有没有更好的方法？

转载于:https://www.cnblogs.com/ctou45/archive/2011/05/06/2039029.html