在一张表上有多个重复的数据，怎么做才能找出不同数据的最后一条记录。
ID stu_id endDate
1 10 2011/10/10
2 10 2011/10/11
3 11 2011/10/12
4 11 2011/10/13
5 12 2011/10/14
6 12 2011/10/15
就是找出
stu_id=10的最后时间
stu_id=11的最后时间
......
------解决方案--------------------
select * from tb a
where not exists(select 1 from tb where stu_id=a.stu_id and endDate>a.endDate)
------解决方案--------------------
select * from tb where enddate in(select max(enddate) from tb group by (stu_id))
------解决方案--------------------
- SQL code
--sql 2005以上版本select * from (select *,rn=row_number()over(partition by stu_id order by enddate)from tb)awhere rn=2--sql 2000--1、select * from tb a where not exists(select 1 from tb where stu_id=a.stu_id and endDate>a.endDate)--2、select * from tb where enddate in(select max(enddate) from tb group by (stu_id))
------解决方案--------------------
select * into 新表（可以随意命名，自动根据旧表生成新表表结构） from 旧表 where id in (select id from 旧表 where enddate in(select max(enddate) from tb group by (stu_id)) )
------解决方案--------------------
SELECT stu_id MAX(enDate) FROM tableName GROUP BY stu_id
------解决方案--------------------
SELECT stu_id ,MAX(enDate) AS enDate FROM tableName GROUP BY stu_id
上句忘加逗号。
------解决方案--------------------
- SQL code
WITH test (ID, stu_id ,endDate) AS ( SELECT 1, 10, '2011/10/10' UNION ALL select 2, 10, '2011/10/11' UNION ALL select 3, 11, '2011/10/12' UNION ALL select 4 ,11, '2011/10/13' UNION ALL select 5 ,12, '2011/10/14' UNION ALL select 6 ,12, '2011/10/15') SELECT * FROM test a WHERE EXISTS(SELECT 1 FROM ( SELECT stu_id,MAX(enddate)enddate FROM test GROUP BY stu_id) b WHERE a.stu_id=b.stu_id AND a.enddate=b.enddate) /* ID stu_id endDate ----------- ----------- ---------- 2 10 2011/10/11 4 11 2011/10/13 6 12 2011/10/15 (3 行受影响) */