解决方案 »
- 分区视图 数据散列 数据组的问题
- ALTER TABLE a ADD aId INT(10) UNSIGNED DEFAULT '-1' 为什么失败
- mysql 语句,select 1+2+3+4 as abc from table1 where abc>0
- 邮箱数据库
- 帮忙,80万mysql 如何提高速度?(附:数据库结构 SQL语句)
- 在哪下载MySQL啊?
- 请教postgres数据库的异地备份脚本?
- 安装mysql,安装不了!
- 请教:mysql for win98及win2000的下载地址
- 关于字节数怎么算的?
- 表1与表2每行相比较,若比表2的值小,表2第二列值+1,怎么实现
- asp.net连接mysql报这个错,是什么意思呢?网站和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),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;