谢谢你了,得行了.我还有一种方式. SELECT * FROM ( SELECT tMoney0,tMoney1,tranDate,NULL AS re FROM tempTB UNION ALL SELECT SUM(tMoney0),SUM(tMoney1),tranDate,CONCAT(tranDate,'日汇总') FROM tempTB GROUP BY tranDate WITH ROLLUP UNION ALL SELECT SUM(tMoney0),SUM(tMoney1),MAX(tranDate),CONCAT(DATE_FORMAT(tranDate,'%Y-%c'),'月汇总') FROM tempTB GROUP BY DATE_FORMAT(tranDate,'%Y-%m') ) AS t ORDER BY tranDate IS NULL,tranDate,re;
-> from(
-> select tMoney0,tMoney1,tranDate,null as re
-> from tempTB
-> union all
-> select sum(tMoney0),sum(tMoney1),tranDate ,concat(tranDate,'汇总')
-> from tempTB
-> group by tranDate
-> union all
-> select sum(tMoney0),sum(tMoney1),null ,concat('总汇总')
-> from tempTB
-> ) t
-> order by tranDate is null,tranDate,re ;
+---------+---------+-----------+---------------+
| tMoney0 | tMoney1 | tranDate | re |
+---------+---------+-----------+---------------+
| 120.00 | 0.00 | 2013-2-12 | NULL |
| 100.00 | 0.00 | 2013-2-12 | NULL |
| 220.00 | 0.00 | 2013-2-12 | 2013-2-12汇总 |
| 150.00 | 0.00 | 2013-2-13 | NULL |
| 0.00 | 200.00 | 2013-2-13 | NULL |
| 0.00 | 1000.00 | 2013-2-13 | NULL |
| 100.00 | 0.00 | 2013-2-13 | NULL |
| 250.00 | 1200.00 | 2013-2-13 | 2013-2-13汇总 |
| 0.00 | 200.00 | 2013-2-14 | NULL |
| 100.00 | 0.00 | 2013-2-14 | NULL |
| 100.00 | 0.00 | 2013-2-14 | NULL |
| 200.00 | 200.00 | 2013-2-14 | 2013-2-14汇总 |
| 100.00 | 0.00 | 2013-3-14 | NULL |
| 100.00 | 0.00 | 2013-3-14 | NULL |
| 200.00 | 0.00 | 2013-3-14 | 2013-3-14汇总 |
| 100.00 | 0.00 | 2013-4-14 | NULL |
| 100.00 | 0.00 | 2013-4-14 | NULL |
| 200.00 | 0.00 | 2013-4-14 | 2013-4-14汇总 |
| 1070.00 | 1400.00 | NULL | 总汇总 |
+---------+---------+-----------+---------------+
19 rows in set (0.10 sec)mysql>
-> from(
-> select tMoney0,tMoney1,tranDate,null as re
-> from tempTB
-> union all
-> select sum(tMoney0),sum(tMoney1),tranDate ,concat(tranDate,'汇总')
-> from tempTB
-> group by tranDate
-> union all
-> select sum(tMoney0),sum(tMoney1),MAX(tranDate) ,CONCAT(date_format(tranDate,'%Y-%c'),'月汇总')
-> from tempTB
-> group by date_format(tranDate,'%Y-%m')
-> union all
-> select sum(tMoney0),sum(tMoney1),null ,'总汇总'
-> from tempTB
-> ) t
-> order by tranDate is null,tranDate,re ;
+---------+---------+-----------+---------------+
| tMoney0 | tMoney1 | tranDate | re |
+---------+---------+-----------+---------------+
| 100.00 | 0.00 | 2013-2-12 | NULL |
| 120.00 | 0.00 | 2013-2-12 | NULL |
| 220.00 | 0.00 | 2013-2-12 | 2013-2-12汇总 |
| 100.00 | 0.00 | 2013-2-13 | NULL |
| 150.00 | 0.00 | 2013-2-13 | NULL |
| 0.00 | 200.00 | 2013-2-13 | NULL |
| 0.00 | 1000.00 | 2013-2-13 | NULL |
| 250.00 | 1200.00 | 2013-2-13 | 2013-2-13汇总 |
| 100.00 | 0.00 | 2013-2-14 | NULL |
| 0.00 | 200.00 | 2013-2-14 | NULL |
| 100.00 | 0.00 | 2013-2-14 | NULL |
| 200.00 | 200.00 | 2013-2-14 | 2013-2-14汇总 |
| 670.00 | 1400.00 | 2013-2-14 | 2013-2月汇总 |
| 100.00 | 0.00 | 2013-3-14 | NULL |
| 100.00 | 0.00 | 2013-3-14 | NULL |
| 200.00 | 0.00 | 2013-3-14 | 2013-3-14汇总 |
| 200.00 | 0.00 | 2013-3-14 | 2013-3月汇总 |
| 100.00 | 0.00 | 2013-4-14 | NULL |
| 100.00 | 0.00 | 2013-4-14 | NULL |
| 200.00 | 0.00 | 2013-4-14 | 2013-4-14汇总 |
| 200.00 | 0.00 | 2013-4-14 | 2013-4月汇总 |
| 1070.00 | 1400.00 | NULL | 总汇总 |
+---------+---------+-----------+---------------+
22 rows in set (0.00 sec)mysql>
SELECT * FROM (
SELECT tMoney0,tMoney1,tranDate,NULL AS re FROM tempTB
UNION ALL
SELECT SUM(tMoney0),SUM(tMoney1),tranDate,CONCAT(tranDate,'日汇总') FROM tempTB GROUP BY tranDate WITH ROLLUP
UNION ALL
SELECT SUM(tMoney0),SUM(tMoney1),MAX(tranDate),CONCAT(DATE_FORMAT(tranDate,'%Y-%c'),'月汇总') FROM tempTB GROUP BY DATE_FORMAT(tranDate,'%Y-%m')
) AS t
ORDER BY tranDate IS NULL,tranDate,re;