WITH TABLE monthlydata AS
(
SELECT sum(xxx) AS totalcases,
id,
month(date) AS [month]
FROM yourtable
GROUP BY id,
month(date)
)
对上方表进行操作:
declare @monthIndex as table
(
[month] INT
)
DECLARE @hid INT;
SET @hid=1;
WHILE @hid <= 12
BEGIN
INSERT INTO @monthIndex VALUES(@hid)
SET @hid = @hid + 1;
END
然后进行判断
select
i.siteid,
mindex.month,
sum(totalcases ) as SumUpRecCases
from MonthlyData, @monthIndex mindex
where i.month <= mindex.month
group by i.siteid, mindex.month
最终会显示多行
文章标题 | SQL-实现Monthly SumUp的统计 |
发布日期 | 2016-03-12 |
文章分类 | Tech |
相关标签 | #DB #SQL |