22,209
社区成员
发帖
与我相关
我的任务
分享
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 行受影响)
*/
--sql 2005以上版本
select * from
(select *,rn=row_number()over(partition by stu_id order by enddate)from tb)a
where 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))