一个Sql分组取最大值问题

表格：
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到本月

8 个解决方案

#1

--> 测试数据：[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 ALL
SELECT 1,'2011-1-12',9 UNION ALL
SELECT 1,'2011-3-1',13 UNION ALL
SELECT 1,'2011-5-23',11 UNION ALL
SELECT 1,'2012-5-29',7 UNION ALL
SELECT 2,'2011-1-1',10 UNION ALL
SELECT 2,'2011-1-12',9 UNION ALL
SELECT 2,'2011-3-1',13 UNION ALL
SELECT 2,'2011-5-23',11 UNION ALL
SELECT 2,'2012-5-29',13 UNION ALL
SELECT 3,'2011-1-1',10 UNION ALL
SELECT 3,'2011-4-12',9 UNION ALL
SELECT 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 9
1           2011-01-31 00:00:00.000 9
1           2011-03-31 00:00:00.000 13
1           2011-05-31 00:00:00.000 11
1           2012-05-31 00:00:00.000 13
2           2011-01-31 00:00:00.000 9
2           2011-01-31 00:00:00.000 9
2           2011-03-31 00:00:00.000 13
2           2011-05-31 00:00:00.000 11
2           2012-05-31 00:00:00.000 13
3           2011-01-31 00:00:00.000 9
3           2011-04-30 00:00:00.000 9
3           2011-05-31 00:00:00.000 11

(13 行受影响)


*/

#2

--> 测试数据：[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 ALL
SELECT 1,'2011-1-12',9 UNION ALL
SELECT 1,'2011-3-1',13 UNION ALL
SELECT 1,'2011-5-23',11 UNION ALL
SELECT 1,'2012-5-29',7 UNION ALL
SELECT 2,'2011-1-1',10 UNION ALL
SELECT 2,'2011-1-12',9 UNION ALL
SELECT 2,'2011-3-1',13 UNION ALL
SELECT 2,'2011-5-23',11 UNION ALL
SELECT 2,'2012-5-29',13 UNION ALL
SELECT 3,'2011-1-1',10 UNION ALL
SELECT 3,'2011-4-12',9 UNION ALL
SELECT 3,'2011-5-23',13
--------------开始查询--------------------------

SELECT [a],DATEADD(mm,DATEDIFF(mm,1,[b])+1,-1) AS [b],s.[cnt]
FROM [tb] AS t
CROSS 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 9
1           2011-01-31 00:00:00.000 9
1           2011-03-31 00:00:00.000 13
1           2011-05-31 00:00:00.000 11
1           2012-05-31 00:00:00.000 13
2           2011-01-31 00:00:00.000 9
2           2011-01-31 00:00:00.000 9
2           2011-03-31 00:00:00.000 13
2           2011-05-31 00:00:00.000 11
2           2012-05-31 00:00:00.000 13
3           2011-01-31 00:00:00.000 9
3           2011-04-30 00:00:00.000 9
3           2011-05-31 00:00:00.000 11

(13 行受影响)


*/

#3

我擦。。。。。。。。。。。。。。。。。。。。。。。。。

#4

declare @test table(a int, b datetime, cnt int)
insert into @test
select 1, '2011-1-1', 10 union all
select 1, '2011-1-12', 9 union all
select 1, '2011-3-1', 13 union all
select 1, '2011-5-23', 11 union all
select 1, '2012-5-29', 7 union all
select 2, '2011-1-1', 10 union all
select 2, '2011-1-12', 9 union all
select 2, '2011-3-1', 13 union all
select 2, '2011-5-23', 11 union all
select 2, '2012-5-29', 13 union all
select 3, '2011-1-1', 10 union all
select 3, '2011-4-12', 9 union all
select 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 9
1           2011-02-01 9
1           2011-03-01 13
1           2011-04-01 13
1           2011-05-23 11
1           2011-06-01 11
1           2011-07-01 11
1           2011-08-01 11
1           2011-09-01 11
1           2011-10-01 11
1           2011-11-01 11
1           2011-12-01 11
1           2012-01-01 11
1           2012-02-01 11
1           2012-03-01 11
1           2012-04-01 11
1           2012-05-29 7
2           2011-01-12 9
2           2011-02-01 9
2           2011-03-01 13
2           2011-04-01 13
2           2011-05-23 11
2           2011-06-01 11
2           2011-07-01 11
2           2011-08-01 11
2           2011-09-01 11
2           2011-10-01 11
2           2011-11-01 11
2           2011-12-01 11
2           2012-01-01 11
2           2012-02-01 11
2           2012-03-01 11
2           2012-04-01 11
2           2012-05-29 13
3           2011-01-01 10
3           2011-02-01 10
3           2011-03-01 10
3           2011-04-12 9
3           2011-05-23 13
3           2011-06-01 13
3           2011-07-01 13
3           2011-08-01 13
3           2011-09-01 13
3           2011-10-01 13
3           2011-11-01 13
3           2011-12-01 13
3           2012-01-01 13
3           2012-02-01 13
3           2012-03-01 13
3           2012-04-01 13
3           2012-05-01 13
*/

#5

有没有更好的解决办法了，数据量有点大

#6

引用 4 楼  的回复:

SQL code

declare @test table(a int, b datetime, cnt int)
insert into @test
select 1, '2011-1-1', 10 union all
select 1, '2011-1-12', 9 union all
select 1, '2011-3-1', 13 union all
select 1, '2011-5……

有没有更好的解决办法了，数据量有点大。。。

#7

引用 6 楼  的回复:

引用 4 楼 的回复:

SQL code

declare @test table(a int, b datetime, cnt int)
insert into @test
select 1, '2011-1-1', 10 union all
select 1, '2011-1-12', 9 union all
select 1, '2011-3-1', 13 union al……

貌似没有更好的方法吧，处理逻辑基本上都是先构造月分，然后与原表匹配。
我想既然数据量大，你应该通过条件进行筛选（比如字段a），而不是把所有的数据都弄出来

#8

引用 7 楼  的回复:

引用 6 楼  的回复:
引用 4 楼 的回复:

SQL code

declare @test table(a int, b datetime, cnt int)
insert into @test
select 1, '2011-1-1', 10 union all
select 1, '2011-1-12', 9 union all
select 1, '2011-3-……

谢谢你了，我这就给你整分数