我的表结构如下CREATE TABLE `Case` (
`ID` varchar(50) NOT NULL,
`CType` varchar(50) NOT NULL,
`DiscoveryTime` datetime NOT NULL,
PRIMARY KEY (`ID`)
)
现在我想统计一年12个月每个月CType的数量,如果某个月没有数据就是0,这个sql怎么写啊 求sql大神帮解答下啊 在线等啊
`ID` varchar(50) NOT NULL,
`CType` varchar(50) NOT NULL,
`DiscoveryTime` datetime NOT NULL,
PRIMARY KEY (`ID`)
)
现在我想统计一年12个月每个月CType的数量,如果某个月没有数据就是0,这个sql怎么写啊 求sql大神帮解答下啊 在线等啊
先生成一个 年月表T,有字段 year_month ,mstart,mend插入12条记录,记录类似于:2015-01,2015-01-01,2015-02-01然后:
select year_month,
count(c.ctype) as ctype
from T
left join case c
on c.DiscoveryTime>=t.mstart and c.DiscoveryTime< m.end
group by year_month
SELECT t1.mh AS `月份`, IFNULL(t2.stype, 0) AS `数量` FROM
(
SELECT (@i:=@i+1) AS mh
FROM
(
SELECT 1 AS tid UNION ALL SELECT 1 AS tid UNION ALL
SELECT 1 AS tid UNION ALL SELECT 1 AS tid UNION ALL
SELECT 1 AS tid UNION ALL SELECT 1 AS tid UNION ALL
SELECT 1 AS tid UNION ALL SELECT 1 AS tid UNION ALL
SELECT 1 AS tid UNION ALL SELECT 1 AS tid UNION ALL
SELECT 1 AS tid UNION ALL SELECT 1 AS tid
) AS t
) AS t1
LEFT JOIN
(SELECT
MONTH(a.DiscoveryTime) AS mh,
SUM(a.ctype) AS stype FROM `case` AS a
WHERE YEAR(a.DiscoveryTime) = '2015'
GROUP BY mh) AS t2
ON t1.mh = t2.mh
ORDER BY t1.mh