if OBJECT_ID('tb') is not null drop table tb gocreate table tb ( stid int, nexttm datetime )insert into tb select 1015,'2014-01-12 20:33:00.000' union all select 1015,'2014-01-12 20:33:00.000' union all select 1001,'2013-10-10 13:47:00.000' union all select 1001,'2013-10-10 13:47:00.000' union all select 1015,'2013-10-19 20:33:00.000' union all select 1015,'2013-11-12 20:33:00.000' union all select 1015,'2014-01-12 20:33:00.000' select stid, nexttm as rtdid from ( select *, ROW_NUMBER() over(partition by stid order by nexttm asc) as rownum from tb )t where rownum = 1/* stid rtdid 1001 2013-10-10 13:47:00.000 1015 2013-10-19 20:33:00.000 */
select stid,min(rtdid) as rtdid from tb group by stid
再请教一下,还有点问题,因为我返回的不止这两个字段,我要更多字段的话,比如除了stid外还有 UTID,UTNM,但我还想只靠stid实现这个效果呢上面的求min值只适合于返回stid和rtdid字段,不适合返回多个字段的情况,否则就会有问题的:select stid, UTID, UTNM nexttm as rtdid from ( select *, ROW_NUMBER() over(partition by stid order by nexttm asc) as rownum from tb )t where rownum = 1
if OBJECT_ID('tb') is not null
drop table tb
gocreate table tb
(
stid int,
nexttm datetime
)insert into tb
select 1015,'2014-01-12 20:33:00.000' union all
select 1015,'2014-01-12 20:33:00.000' union all
select 1001,'2013-10-10 13:47:00.000' union all
select 1001,'2013-10-10 13:47:00.000' union all
select 1015,'2013-10-19 20:33:00.000' union all
select 1015,'2013-11-12 20:33:00.000' union all
select 1015,'2014-01-12 20:33:00.000'
select stid,
nexttm as rtdid
from
(
select *,
ROW_NUMBER() over(partition by stid
order by nexttm asc) as rownum
from tb
)t
where rownum = 1/*
stid rtdid
1001 2013-10-10 13:47:00.000
1015 2013-10-19 20:33:00.000
*/
from tb
group by stid
再请教一下,还有点问题,因为我返回的不止这两个字段,我要更多字段的话,比如除了stid外还有 UTID,UTNM,但我还想只靠stid实现这个效果呢
再请教一下,还有点问题,因为我返回的不止这两个字段,我要更多字段的话,比如除了stid外还有 UTID,UTNM,但我还想只靠stid实现这个效果呢上面的求min值只适合于返回stid和rtdid字段,不适合返回多个字段的情况,否则就会有问题的:select stid,
UTID,
UTNM
nexttm as rtdid
from
(
select *,
ROW_NUMBER() over(partition by stid
order by nexttm asc) as rownum
from tb
)t
where rownum = 1