select code,(select top 1 from (select top N * from tablename where code=a.code order by date)b order by date desc) from tablename group by code
select * from tablename a where [DATE]=(select top 1 [date] from (select top 2 * from tablename where code=a.code order by [date]) as x order by [date] desc ) 呵呵,好像有问题,没测试
select code,(select top 1 [date] from (select top N * [date] from tablename where code=a.code order by date)b order by date desc) from tablename a group by code
更正select distinct code,(select top 1 [date] from (select top 2 [date] from tablename where code=a.code order by date)b order by date desc) [date] from tablename a order by code
declare @t table(dd varchar(4),dt varchar(10)) insert into @t values('0001','2003-10-01') insert into @t values('0001','2003-10-02') insert into @t values('0001','2003-10-03') insert into @t values('0001','2003-10-04') insert into @t values('0002','2003-10-01') insert into @t values('0002','2003-10-03') insert into @t values('0002','2003-10-04') insert into @t values('0002','2003-10-06') insert into @t values('0003','2003-10-01') insert into @t values('0003','2003-10-02') insert into @t values('0003','2003-10-03') insert into @t values('0003','2003-10-04') select c.dd,min(c.dt) as dt from (select a.* from @t a,(select dd,min(dt) as dt from @t group by dd)b where a.dt>b.dt and a.dd=b.dd)c group by c.dd
where [DATE]=(select top 1 [date] from (select top 2 * from tablename where code=a.code order by [date]) as x order by [date] desc )
呵呵,好像有问题,没测试
insert into @t values('0001','2003-10-01')
insert into @t values('0001','2003-10-02')
insert into @t values('0001','2003-10-03')
insert into @t values('0001','2003-10-04')
insert into @t values('0002','2003-10-01')
insert into @t values('0002','2003-10-03')
insert into @t values('0002','2003-10-04')
insert into @t values('0002','2003-10-06')
insert into @t values('0003','2003-10-01')
insert into @t values('0003','2003-10-02')
insert into @t values('0003','2003-10-03')
insert into @t values('0003','2003-10-04')
select c.dd,min(c.dt) as dt from (select a.* from @t a,(select dd,min(dt) as dt from @t group by dd)b
where a.dt>b.dt and a.dd=b.dd)c group by c.dd
0001的第二条记录 2003-10-02
0002的第二条记录 2003-10-03
0003的第二条记录 2003-10-02--------------------------------------------
!!!谢谢各位的帮助!!!
[email protected]
--------------------------------------------
您的查询在 CODE,DATE记录唯一的时候是正确的,可是如果有重复的数据
0001 2003-10-01
0001 2003-10-01
0001 2003-10-02
0001 2003-10-03
0001 2003-10-04
0002 2003-10-01
0002 2003-10-02
0002 2003-10-03
0002 2003-10-04
0002 2003-10-06
0003 2003-10-01
0003 2003-10-02
0003 2003-10-05
0003 2003-10-06
就不正确了