日期:2014-05-17  浏览次数:20527 次

取分组 日期最大的数据
将数据 分组,取得日期最大的记录,如果日期最大有相同 就一起取
如下:[code=SQL][/code]
ID Date
1 2012-3-10
1 2012-3-10
1 2012-3-4
2 2012-2-10
2 2012-2-10
2 2012-3-8
2 2012-3-9
3 2012-5-12
3 2012-4-12
3 2012-1-2
4 2012-5-5
5 2012-3-7
6 2012-4-2
7 2012-2-1
要 获得这样的结果:

ID Date
1 2012-3-10
1 2012-3-10
2 2012-3-9
3 2012-5-12
4 2012-5-5
5 2012-3-7
6 2012-4-2
7 2012-2-1


------解决方案--------------------
SELECT * FROM TB T WHERE DATE=(SELECT MAX(DATE) FROM TB WHERE ID=T.ID)
------解决方案--------------------
1楼的结果不对
SQL code
WITH test (ID,    [Date])
 AS 
 (
 SELECT 1,    '2012-3-10'
 UNION ALL SELECT 1,    '2012-3-10'
 UNION ALL SELECT 1,    '2012-3-4'
 UNION ALL SELECT 2,    '2012-2-10'
 UNION ALL SELECT 2,    '2012-2-10'
 UNION ALL SELECT 2,    '2012-3-8'
 UNION ALL SELECT 2,    '2012-3-9'
 UNION ALL SELECT 3,    '2012-5-12'
 UNION ALL SELECT 3,    '2012-4-12'
 UNION ALL SELECT 3,    '2012-1-2'
 UNION ALL SELECT 4,    '2012-5-5'
 UNION ALL SELECT 5,    '2012-3-7'
 UNION ALL SELECT 6,    '2012-4-2'
 UNION ALL SELECT 7,    '2012-2-1'
 )
 SELECT id,MIN ([Date])[Date]
 FROM Test 
 GROUP BY id
 
 /*
 id          Date
 ----------- ---------
 1           2012-3-10
 2           2012-2-10
 3           2012-1-2
 4           2012-5-5
 5           2012-3-7
 6           2012-4-2
 7           2012-2-1
 
 (7 行受影响)
 */

------解决方案--------------------
探讨
1楼的结果不对

SQL code

WITH test (ID, [Date])
AS
(
SELECT 1, '2012-3-10'
UNION ALL SELECT 1, '2012-3-10'
UNION ALL SELECT 1, '2012-3-4'
UNION ALL SELECT 2, '2012-2-10'
UN……

------解决方案--------------------
探讨
原来倒序了.....我说怎么执行你的和楼主结果不一样呢