22,206
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([DT] datetime,[Qty] int)
insert #TA select '2009-12-23',5
union all select '2010-01-20',1
union all select '2010-01-25',2
union all select '2010-01-26',3
union all select '2010-02-27',4
union all select '2010-03-25',5
union all select '2010-12-26',100
union all select '2011-01-23',6
union all select '2011-01-25',7
union all select '2011-02-03',8
union all select '2011-03-03',9
union all select '2011-03-25',10
union all select '2011-03-26',11
;WITH CET AS
(
select case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end as YM, SUM(Qty) SumQty
from #TA
group by case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end
),c2 as(
SELECT O1.YM, SUM(O2.SumQty) AS totalqty
FROM CET AS O1
JOIN CET AS O2
ON O2.YM <= O1.YM and o2.YM>dateadd(month,-12,o1.YM)
GROUP BY O1.YM
--ORDER BY O1.YM
)
select a.ym,isnull(b.totalqty,0) as totalqty from(
select dateadd(m,b.number,a.ym1)ym
from (select min(ym) as ym1,max(ym) as ym2 from cet) a,master..spt_values b
where b.type='p' and dateadd(m,b.number,a.ym1)<=a.ym2
)a left join c2 b on a.ym=b.ym
/*
ym totalqty
----------------------- -----------
2010-01-25 00:00:00.000 5
2010-02-25 00:00:00.000 8
2010-03-25 00:00:00.000 11
2010-04-25 00:00:00.000 20
2010-05-25 00:00:00.000 0
2010-06-25 00:00:00.000 0
2010-07-25 00:00:00.000 0
2010-08-25 00:00:00.000 0
2010-09-25 00:00:00.000 0
2010-10-25 00:00:00.000 0
2010-11-25 00:00:00.000 0
2010-12-25 00:00:00.000 0
2011-01-25 00:00:00.000 0
2011-02-25 00:00:00.000 125
2011-03-25 00:00:00.000 130
2011-04-25 00:00:00.000 140
2011-05-25 00:00:00.000 151
(17 行受影响)
*/
dbo.nums 是数字辅助表
--简易数字辅助表
select number from master..spt_values where type='p' and number between 1 and 10
--创建一般的数字辅助表
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 10000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([DT] datetime,[Qty] int)
insert #TA select '2009-12-23',5
union all select '2010-01-20',1
union all select '2010-01-25',2
union all select '2010-01-26',3
union all select '2010-02-27',4
union all select '2010-03-25',5
union all select '2010-12-26',100
union all select '2011-01-23',6
union all select '2011-01-25',7
union all select '2011-02-03',8
union all select '2011-03-03',9
union all select '2011-03-25',10
union all select '2011-03-26',11
;WITH CET0 AS
(
SELECT * FROM #TA
UNION ALL
SELECT dateadd(mm,1 - n ,GETDATE()),0--GETDATE() 可以考虑换成 (select max(DT) from #TA)
FROM dbo.Nums
WHERE n <= DATEDIFF(mm, (select min(DT) from #TA), GETDATE())),--GETDATE() 可以考虑换成 (select max(DT) from #TA)
CET AS
(
select case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end as YM, SUM(Qty) SumQty
from CET0
group by case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end
)
SELECT O1.YM, SUM(O2.SumQty) AS totalqty
FROM CET AS O1
JOIN CET AS O2
ON O2.YM <= O1.YM and o2.YM>dateadd(month,-12,o1.YM)
GROUP BY O1.YM
ORDER BY O1.YM;
/*
(13 行受影响)
YM totalqty
---------- -----------
2010-01-25 5
2010-02-25 8
2010-03-25 11
2010-04-25 20
2010-05-25 20
2010-06-25 20
2010-07-25 20
2010-08-25 20
2010-09-25 20
2010-10-25 20
2010-11-25 20
2010-12-25 20
2011-01-25 15
2011-02-25 125
2011-03-25 130
2011-04-25 140
2011-05-25 151
2011-06-25 151
2011-07-25 151
2011-08-25 151
2011-09-25 151
2011-10-25 151
2011-11-25 151
2011-12-25 151
2012-01-25 151
2012-02-25 38
2012-03-25 30
2012-04-25 11
2012-05-25 0
2012-06-25 0
2012-07-25 0
2012-08-25 0
2012-09-25 0
2012-10-25 0
2012-11-25 0
2012-12-25 0
2013-01-25 0
2013-02-25 0
2013-03-25 0
(39 行受影响)*/
;WITH CTE0 AS (SELECT ... FROM ... WHERE DT>=... AND DT<=... UNION ALL '某月到某月的空数据')
,CTE AS(
'上面你写的搜索语句,将原来的表换成CTE0'
)
至于动态效果,即动态添加空数据,这个可以在程序上实现,然后限定查询时间必填