一个Sql分组取最大值有关问题
日期：2014-05-17 浏览次数：20533 次
一个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到本月
------解决方案--------------------
表格：
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 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 行受影响)
*/
------解决方案--------------------
- 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 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 行受影响)
*/
------解决方案--------------------
- 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 al
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
