表格：
a b cnt
1 2011-1-1 10
1 2011-1-12 9
1 2011-3-1 13
1 2011-5-23 11
1 2012-5-29 7
2 2011-1-1 10
2 2011-1-12 9
2 2011-3-1 13
2 2011-5-23 11
2 2012-5-29 13
3 2011-1-1 10
3 2011-4-12 9
3 2011-5-23 13
需要结果：
a b cnt
1 2011-1-31 9
1 2011-2-28 9
1 2011-3-31 13
1 2011-4-30 13
1 2011-5-31 7
2 2011-1-31 9
2 2011-2-28 9
2 2011-3-31 13
2 2011-4-30 13
2 2011-5-31 13
3 2011-1-31 10
3 2011-2-28 10
3 2011-3-31 10
3 2011-4-30 9
3 2011-5-31 13
找每个月最大日期对于的cnt
如果当月没有，取上月最大CNT到本月
------解决方案--------------------
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([a] INT,[b] DATETIME,[cnt] INT)INSERT [tb]SELECT 1,'2011-1-1',10 UNION ALLSELECT 1,'2011-1-12',9 UNION ALLSELECT 1,'2011-3-1',13 UNION ALLSELECT 1,'2011-5-23',11 UNION ALLSELECT 1,'2012-5-29',7 UNION ALLSELECT 2,'2011-1-1',10 UNION ALLSELECT 2,'2011-1-12',9 UNION ALLSELECT 2,'2011-3-1',13 UNION ALLSELECT 2,'2011-5-23',11 UNION ALLSELECT 2,'2012-5-29',13 UNION ALLSELECT 3,'2011-1-1',10 UNION ALLSELECT 3,'2011-4-12',9 UNION ALLSELECT 3,'2011-5-23',13--------------开始查询--------------------------SELECT [a],DATEADD(mm,DATEDIFF(mm,1,[b])+1,-1), (SELECT TOP 1 [cnt] FROM tb WHERE CONVERT(varchar(7),[b],120)=CONVERT(varchar(7),t.[b],120) ORDER BY [b] DESC)FROM [tb] AS t----------------结果----------------------------/* a ----------- ----------------------- -----------1 2011-01-31 00:00:00.000 91 2011-01-31 00:00:00.000 91 2011-03-31 00:00:00.000 131 2011-05-31 00:00:00.000 111 2012-05-31 00:00:00.000 132 2011-01-31 00:00:00.000 92 2011-01-31 00:00:00.000 92 2011-03-31 00:00:00.000 132 2011-05-31 00:00:00.000 112 2012-05-31 00:00:00.000 133 2011-01-31 00:00:00.000 93 2011-04-30 00:00:00.000 93 2011-05-31 00:00:00.000 11(13 行受影响)*/
------解决方案--------------------
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([a] INT,[b] DATETIME,[cnt] INT)INSERT [tb]SELECT 1,'2011-1-1',10 UNION ALLSELECT 1,'2011-1-12',9 UNION ALLSELECT 1,'2011-3-1',13 UNION ALLSELECT 1,'2011-5-23',11 UNION ALLSELECT 1,'2012-5-29',7 UNION ALLSELECT 2,'2011-1-1',10 UNION ALLSELECT 2,'2011-1-12',9 UNION ALLSELECT 2,'2011-3-1',13 UNION ALLSELECT 2,'2011-5-23',11 UNION ALLSELECT 2,'2012-5-29',13 UNION ALLSELECT 3,'2011-1-1',10 UNION ALLSELECT 3,'2011-4-12',9 UNION ALLSELECT 3,'2011-5-23',13--------------开始查询--------------------------SELECT [a],DATEADD(mm,DATEDIFF(mm,1,[b])+1,-1) AS [b],s.[cnt]FROM [tb] AS tCROSS APPLY(SELECT TOP 1 [cnt] FROM tb WHERE CONVERT(varchar(7),[b],120)=CONVERT(varchar(7),t.[b],120) ORDER BY [b] DESC) s----------------结果----------------------------/* a b cnt----------- ----------------------- -----------1 2011-01-31 00:00:00.000 91 2011-01-31 00:00:00.000 91 2011-03-31 00:00:00.000 131 2011-05-31 00:00:00.000 111 2012-05-31 00:00:00.000 132 2011-01-31 00:00:00.000 92 2011-01-31 00:00:00.000 92 2011-03-31 00:00:00.000 132 2011-05-31 00:00:00.000 112 2012-05-31 00:00:00.000 133 2011-01-31 00:00:00.000 93 2011-04-30 00:00:00.000 93 2011-05-31 00:00:00.000 11(13 行受影响)*/
------解决方案--------------------
- SQL code
declare @test table(a int, b datetime, cnt int)insert into @testselect 1, '2011-1-1', 10 union allselect 1, '2011-1-12', 9 union allselect 1, '2011-3-1', 13 union allselect 1, '2011-5-23', 11 union allselect 1, '2012-5-29', 7 union allselect 2, '2011-1-1', 10 union allselect 2, '2011-1-12', 9 union allselect 2, '2011-3-1', 13 union allselect 2, '2011-5-23', 11 union allselect 2, '2012-5-29', 13 union allselect 3, '2011-1-1', 10 union allselect 3, '2011-4-12', 9 union allselect 3, '2011-5-23', 13--开始查询;with cte as( select row_number() over(partition by e.a order by e.a,e.dt) rn,e.a,isnull(d.b,e.dt) dt,d.cnt from ( select a,b,cnt from ( select row_number() over(partition by a,convert(varchar(6),b,112) order by a,b desc) rn,* from @test )t where t.rn=1 ) d right join ( select c.a,dateadd(mm,number,b._min) dt from master..spt_values a,(select min(b) _min,max(b) _max from @test) b, (select distinct a from @test) c where type='P' and number<=datediff(mm,b._min,b._max) ) e on convert(varchar(6),d.b,112)=convert(varchar(6),e.dt,112) and d.a=e.a)select a,convert(varchar(10),dt,120) b,case when cnt is not null then cnt else (select top 1 cnt from cte where t.a=a and t.rn>rn and cnt is not null order by rn desc) end cnt from cte t--测试结果/*a b cnt----------- ---------- -----------1 2011-01-12 91 2011-02-01 91 2011-03-01 131 2011-04-01 131 2011-05-23 111 2011-06-01 111 2011-07-01 111 2011-08-01 111 2011-09-01 111 2011-10-01 111 2011-11-01 111 2011-12-01 111 2012-01-01 111 2012-02-01 111 2012-03-01 111 2012-04-01 111 2012-05-29 72 2011-01-12 92 2011-02-01 92 2011-03-01 132 2011-04-01 132 2011-05-23 112 2011-06-01 112 2011-07-01 112 2011-08-01 112 2011-09-01 112 2011-10-01 112 2011-11-01 112 2011-12-01 112 2012-01-01 112 2012-02-01 112 2012-03-01 112 2012-04-01 112 2012-05-29 133 2011-01-01 103 2011-02-01 103 2011-03-01 103 2011-04-12 93 2011-05-23 133 2011-06-01 133 2011-07-01 133 2011-08-01 133 2011-09-01 133 2011-10-01 133 2011-11-01 133 2011-12-01 133 2012-01-01 133 2012-02-01 133 2012-03-01 133 2012-04-01 133 2012-05-01 13*/