取分组 日期最大的数据解决方法
取分组 日期最大的数据
将数据 分组,取得日期最大的记录,如果日期最大有相同 就一起取
如下:[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楼的结果不对
将数据 分组,取得日期最大的记录,如果日期最大有相同 就一起取
如下:[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 行受影响) */
------解决方案--------------------
------解决方案--------------------