排序查询后怎么获取第一出现的数据?

Question

create table #test (numbers varchar(50),sdt datetime)
insert into #test values('001','2011-05-03 07:11:23.000')
insert into #test values('005','2011-05-03 07:12:23.000')
insert into #test values('002','2011-05-03 12:11:23.000')
insert into #test values('005','2011-05-03 12:12:23.000')
insert into #test values('002','2011-05-03 13:11:23.000')
insert into #test values('003','2011-05-03 07:13:23.000')
insert into #test values('004','2011-05-03 07:10:23.000')
select *  from #test order by numbersnumbers                                            sdt
-------------------------------------------------- -----------------------
001                                                2011-05-03 07:11:23.000
002                                                2011-05-03 12:11:23.000
002                                                2011-05-03 13:11:23.000
003                                                2011-05-03 07:13:23.000
004                                                2011-05-03 07:10:23.000
005                                                2011-05-03 12:12:23.000
005                                                2011-05-03 07:12:23.000怎么分组查询获取第一出现的数据numbers 

Answer 1

本帖最后由 josy 于 2011-05-05 00:34:08 编辑

  

Answer 2

我想要的结果是001                                                2011-05-03 07:11:23.000
002                                                2011-05-03 12:11:23.000
003                                                2011-05-03 07:13:23.000
004                                                2011-05-03 07:10:23.000
005                                                2011-05-03 12:12:23.000就是排序后,numbers名的第一条记录

Answer 3

select numbers,sdt
from
(
 select *,rn=row_number() over(partition by numbers order by numbers)  
 from #test 
) t
where rn=1sql2000的话可以增加一个自增列

Answer 4

谢谢可以了,再麻烦一下,如果我再加入这些数据.日期不同了,怎么办,其实就是又根据numbers又要根据次数.这样做的目的是判断哪个是员工的上班时间.
insert into #test values('001','2011-05-04 07:11:23.000')
insert into #test values('005','2011-05-04 07:12:23.000')
insert into #test values('002','2011-05-04 12:11:23.000')
insert into #test values('005','2011-05-04 12:12:23.000')
insert into #test values('002','2011-05-04 13:11:23.000')
insert into #test values('003','2011-05-04 07:13:23.000')
insert into #test values('004','2011-05-04 07:10:23.000')
insert into #test values('002','2011-05-04 14:11:23.000')我刚才的思路以为第一次出现就是上班了.结果一天打四次卡的话.我那样做就错了.应该是Numbers号出现的单数为上班.我用number % 2 <> 0 行不通.

Answer 5

上下班时间应该以时间先后顺序来判断吧，不应以查询顺序来判断
你先看看以下语句的结果，再看一下rn是不是你想要的
select *,
rn=row_number() over(partition by numbers,convert(varchar(10),sdt,120) order by sdt)  
from #test
order by numbers,sdt 

Answer 6

--测试数据
numbers                                            sdt
-------------------------------------------------- -----------------------
001                                                2011-05-04 07:11:23.000
005                                                2011-05-04 07:12:23.000
002                                                2011-05-04 12:11:23.000
005                                                2011-05-04 12:12:23.000
002                                                2011-05-04 13:11:23.000
003                                                2011-05-04 07:13:23.000
004                                                2011-05-04 07:10:23.000
002                                                2011-05-05 08:11:23.000
002                                                2011-05-05 12:11:23.000
002                                                2011-05-05 13:11:23.000
002                                                2011-05-05 18:11:23.000
001                                                2011-05-05 07:11:23.000(12 row(s) affected)
--测试结果
numbers                                            sdt                     rn
-------------------------------------------------- ----------------------- --------------------
001                                                2011-05-04 07:11:23.000 1 -这里不对
001                                                2011-05-05 07:11:23.000 1--这里不对
002                                                2011-05-04 12:11:23.000 1
002                                                2011-05-04 13:11:23.000 2
002                                                2011-05-05 08:11:23.000 1
002                                                2011-05-05 12:11:23.000 2
002                                                2011-05-05 13:11:23.000 3
002                                                2011-05-05 18:11:23.000 4
003                                                2011-05-04 07:13:23.000 1
004                                                2011-05-04 07:10:23.000 1
005                                                2011-05-04 07:12:23.000 1
005                                                2011-05-04 12:12:23.000 2(12 row(s) affected)按时间先后排有点不对劲.跨天的rn全是1了,就分不出那个是上班那个是下班时间了

Answer 7

select *,
rn=row_number() over(partition by numbers order by sdt)  
from #test
order by numbers,sdt
1天才打一次卡？那就不要按天分组了 

Answer 8

是的,如果上晚班的话,就是跨天打卡了.
这样OK.我可以通过rn来判断上下班.就一起问你一下了.我通过rn判断的话.用case then好,还是用if好呢create table #PB (numbers varchar(50),BS1 datetime,BX1 datetime,BS2 datetime,BX3 datetime,BS4 datetime,BX5 datetime,BS6 datetime)
这样的一个表.把rn的值添加到这个表里面用哪个判断好

Answer 9

结贴了,josy大牛还能看到吗?我还有两个贴全是一样的问题.麻烦再看一下吧..我通过rn判断的话.用case then好,还是用if好呢create table #PB (numbers varchar(50),BS1 datetime,BX1 datetime,BS2 
datetime,BX3 datetime,BS4 datetime,BX5 datetime,BS6 datetime)