| date | plan | temp |
|---|---|---|
| 2013-01-05 | 1 | 0 |
| 2013-01-06 | 1 | 0 |
| 2013-01-07 | 0 | 1 |
| 2013-02-05 | 1 | 0 |
| 2013-02-06 | 1 | 0 |
| 2013-03-06 | 0 | 1 |
| 2013-04-07 | 1 | 0 |
| 2013-04-08 | 0 | 1 |
| 2013-05-08 | 1 | 0 |
| 2013-07-09 | 0 | 1 |
| 2013-07-10 | 1 | 0 |
想输出2013年各月份的情况，
语句如下：
SELECT SUBSTRING(date, 6, 2) AS mon, COUNT(paln) AS planed, COUNT(temp) AS temp FROM A WHERE SUBSTRING(date, 1, 4)='2013' GROUP BY SUBSTRING(date, 6, 2)
输出结果如下：
| Mon | Plan | Temp |
|---|---|---|
| 01 | 2 | 1 |
| 02 | 2 | 0 |
| 03 | 0 | 1 |
| 04 | 1 | 1 |
| 05 | 1 | 0 |
| 07 | 1 | 1 |
期待效果：
| Mon | Plan | Temp |
|---|---|---|
| 01 | 2 | 1 |
| 02 | 2 | 0 |
| 03 | 0 | 1 |
| 04 | 1 | 1 |
| 05 | 1 | 0 |
| 06 | 0 | 0 |
| 07 | 1 | 1 |
| 08 | 0 | 0 |
| 09 | 0 | 0 |
| 10 | 0 | 0 |
| 11 | 0 | 0 |
| 12 | 0 | 0 |
优化后的SQL:
SELECT MON,SUM(plAnED) AS planed,SUM(temp) AS tempFROM ( SELECT SUBSTRING(date, 6, 2) AS mon,SUM(paln) AS planed,SUM(temp) AS temp FROM A WHERE SUBSTRING(date, 1, 4)='2013' GROUP BY SUBSTRING(date, 6, 2) UNION SELECT '01' as mon,'0' as PLANED,'0' as temp union SELECT '02' as mon,'0' as PLANED,'0' as temp union SELECT '03' as mon,'0' as PLANED,'0' as temp union SELECT '04' as mon,'0' as PLANED,'0' as temp union SELECT '05' as mon,'0' as PLANED,'0' as temp union SELECT '06' as mon,'0' as PLANED,'0' as temp union SELECT '07' as mon,'0' as PLANED,'0' as temp union SELECT '08' as mon,'0' as PLANED,'0' as temp union SELECT '09' as mon,'0' as PLANED,'0' as temp union SELECT '10' as mon,'0' as PLANED,'0' as temp union SELECT '11' as mon,'0' as PLANED,'0' as temp union SELECT '12' as mon,'0' as PLANED,'0' as temp ) B GROUP BY B.mon ORDER BY B.MON
在原有的基础之上，虚拟了1到12 月，plan为0, temp 为0 的12 条数据出来，因为我们使用sum 函数的时候，加上多个0 也不会对统计结果产生影响。
查询结果加上虚拟的12 条数据，联合在一起成一个新表 B, 然后再从B 表进一步sum 和group by
union 跟group by一起使用
通过两个查询分别加括号的方式，改成如下：
(SELECT * FROM t1 WHERE username LIKE 'l%' ORDER BY sroce ASC) UNION (SELECT * FROM t1 WHERE username LIKE '%m%' ORDER BY score ASC)
这种方式的目的是为了让两个结果集先分别order by，然后再对两个结果集进行union。但是你会发现这种方式虽然不报错了，但是两个order by并没有效果，所以应该改成如下：
SELECT * FROM (SELECT * FROM t1 WHERE username LIKE 'l%' ORDER BY score ASC) t3 UNION SELECT * FROM (SELECT * FROM t1 WHERE username LIKE '%m%' ORDER BY score ASC) t4
也就是说，order by不能直接出现在union的子句中，但是可以出现在子句的子句中。
union和union all 的区别。
union会过滤掉两个结果集中重复的行，而union all不会过滤掉重复行。
http://www.cnblogs.com/pcheng/p/5939646.html
网友评论