取分组 日期最大的数据
将数据 分组，取得日期最大的记录，如果日期最大有相同 就一起取
如下：

IDDate
12012-3-10
12012-3-10
12012-3-4
22012-2-10
22012-2-10
22012-3-8
22012-3-9
32012-5-12
32012-4-12
32012-1-2
42012-5-5
52012-3-7
62012-4-2
72012-2-1
要 获得这样的结果：

IDDate
12012-3-10
12012-3-10
22012-3-9
32012-5-12
42012-5-5
52012-3-7
62012-4-2
72012-2-1

[解决办法]
SELECT * FROM TB T WHERE DATE=(SELECT MAX(DATE) FROM TB WHERE ID=T.ID)
[解决办法]
1楼的结果不对

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……

--DROP TABLE #TB
SELECT 1 AS ID, CONVERT(DATETIME,'2012-3-10') DATE INTO #TB
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 * FROM #TB T WHERE DATE=(SELECT MAX(DATE) FROM #TB WHERE ID=T.ID)
/*
ID DATE
----------- ------------------------------------------------------
7 2012-02-01 00:00:00.000
6 2012-04-02 00:00:00.000
5 2012-03-07 00:00:00.000
4 2012-05-05 00:00:00.000
3 2012-05-12 00:00:00.000
2 2012-03-09 00:00:00.000
1 2012-03-10 00:00:00.000
1 2012-03-10 00:00:00.000

（所影响的行数为 8 行）



[解决办法]