---------
ID MainID Time details
1 MK001 2010-11-01 0
2 MK001 2010-11-02 1
3 MK001 2010-12-02 1
4 MK002 2010-11-08 2
5 MK002 2010-11-10 2
要求按time查询结果为(time为varchar型)
2 MK001 2010-11-02 1
4 MK002 2010-11-10 2
ID MainID Time details
1 MK001 2010-11-01 0
2 MK001 2010-11-02 1
3 MK001 2010-12-02 1
4 MK002 2010-11-08 2
5 MK002 2010-11-10 2
要求按time查询结果为(time为varchar型)
2 MK001 2010-11-02 1
4 MK002 2010-11-10 2
(
SELECT ROW_NUMBER()OVER(PARTITION BY MainID ORDER BY ID) AS RN,ID,MainID,Time,details FROM TB
)
SELECT ID,MainID,Time,details FROM CTE WHERE RN=2
(
ID int,
MainID varchar(10),
Time varchar(10),
details int
)
insert into tbname values(1 , 'MK001' , '2010-11-01' , 0)
insert into tbname values(2 , 'MK001' , '2010-11-02' , 1)
insert into tbname values(3 , 'MK001' , '2010-12-02' , 1)
insert into tbname values(4 , 'MK002' , '2010-11-08' , 2)
insert into tbname values(5 , 'MK002' , '2010-11-10' , 2)
go--drop table tbnameselect * from tbname a
where Time in
(
select top 1 Time from tbname
where a.MainID=MainID
order by year(Time) desc,month(Time) desc,day(Time) desc
)
where Time in
(
select top 1 Time from tbname
where year(Time)=year(a.Time) and month(Time)=month(a.Time)
order by year(Time) desc,month(Time) desc,day(Time) desc
)
insert into tb select 1,'MK001','2010-11-01',0
insert into tb select 2,'MK001','2010-11-02',1
insert into tb select 3,'MK001','2010-12-02',1
insert into tb select 4,'MK002','2010-11-08',2
insert into tb select 5,'MK002','2010-11-10',2
go
select a.* from tb a inner join (
select mainid,max(time)time from tb where convert(varchar(7),time,120)='2010-11' group by mainid
)b on a.mainid=b.mainid and a.time=b.time
order by 2
go
drop table tb
/*
ID MainID Time details
----------- ---------- ----------------------- -----------
2 MK001 2010-11-02 00:00:00.000 1
5 MK002 2010-11-10 00:00:00.000 2(2 行受影响)*/
and not exists (select 1 from t1 where left(time,7)='2010-11' and t1.mainid=a.mainid and a.id<t1.id)