按年份按月份统计 - 编程论坛

#2

月祭樱冢2015-08-19 15:59

这是我写的查的start_time的sql

SELECT date_format(start_time,'%Y-%m') bb, SUM(1) aa
FROM t_case
GROUP BY date_format(start_time,'%Y-%m')

怎么加end_time 呢？

#3

月祭樱冢2015-08-20 10:48

搞定了虽然没有人看这一版的人还真少呢

程序代码：

SELECT date_format(start_time,'%Y-%m') as caseTime, SUM(1) as count,'lian' type
FROM t_case
WHERE date_format(start_time,'%Y')=2015 OR date_format(start_time,'%Y')=2014
GROUP BY date_format(start_time,'%Y-%m')

union all

SELECT date_format(end_time,'%Y-%m') as caseTime, SUM(1) as count, 'jiean' type
FROM t_case
WHERE date_format(end_time,'%Y')=2015 OR date_format(end_time,'%Y')=2014
GROUP BY date_format(end_time,'%Y-%m')

#4

donaldlo2015-08-20 19:06

select month1,sum(counta) counta,sum(countb) countb
from
(
SELECT date_format(start_time,'%Y-%m') month1, sum(1) counta,0 countb
FROM test.t_case
GROUP BY date_format(start_time,'%Y-%m')
union all
SELECT date_format(end_time,'%Y-%m') month1,0 counta,sum(1) countb
FROM test.t_case
GROUP BY date_format(end_time,'%Y-%m')
) s
group by month1