27,579
社区成员
发帖
与我相关
我的任务
分享
drop table tb
go
create table tb(
UID varchar(20),工单号 int,工单处理人 varchar(20),
工单类型 varchar(20),
录入时间数据 datetime
)
insert into tb
select 'eadabyas', 1007 ,'admin', 'a', '2013-10-20 12:30:30' union all
select 'tysfdgr', 1008 ,'user007', 'b', '2013-10-20 12:30:31' union all
select 'ad4545tr', 1009 ,'user001','a', '2013-10-20 12:30:35' union all
select 'wersdfdfg', 1000 ,'admin', 'b', '2013-10-20 12:30:39' union all
select 'tasdjuh', 1005 ,'admin', 'b', '2013-10-20 12:30:59'
go
select UID,工单号 ,工单处理人,工单类型,录入时间数据
from
(
select *,
ROW_NUMBER() over(partition by 工单处理人 order by 录入时间数据 desc) rownum
from tb
)t
where rownum = 1
/*
UID 工单号 工单处理人 工单类型 录入时间数据
tasdjuh 1005 admin b 2013-10-20 12:30:59.000
ad4545tr 1009 user001 a 2013-10-20 12:30:35.000
tysfdgr 1008 user007 b 2013-10-20 12:30:31.000
*/
[/quote]
必须是2005版本吗[/quote]
2005、2008都可以的,2000不行,2000没有row_number函数drop table tb
go
create table tb(
UID varchar(20),工单号 int,工单处理人 varchar(20),
工单类型 varchar(20),
录入时间数据 datetime
)
insert into tb
select 'eadabyas', 1007 ,'admin', 'a', '2013-10-20 12:30:30' union all
select 'tysfdgr', 1008 ,'user007', 'b', '2013-10-20 12:30:31' union all
select 'ad4545tr', 1009 ,'user001','a', '2013-10-20 12:30:35' union all
select 'wersdfdfg', 1000 ,'admin', 'b', '2013-10-20 12:30:39' union all
select 'tasdjuh', 1005 ,'admin', 'b', '2013-10-20 12:30:59'
go
select UID,工单号 ,工单处理人,工单类型,录入时间数据
from
(
select *,
ROW_NUMBER() over(partition by 工单处理人 order by 录入时间数据 desc) rownum
from tb
)t
where rownum = 1
/*
UID 工单号 工单处理人 工单类型 录入时间数据
tasdjuh 1005 admin b 2013-10-20 12:30:59.000
ad4545tr 1009 user001 a 2013-10-20 12:30:35.000
tysfdgr 1008 user007 b 2013-10-20 12:30:31.000
*/
[/quote]
必须是2005版本吗drop table tb
go
create table tb(
UID varchar(20),工单号 int,工单处理人 varchar(20),
工单类型 varchar(20),
录入时间数据 datetime
)
insert into tb
select 'eadabyas', 1007 ,'admin', 'a', '2013-10-20 12:30:30' union all
select 'tysfdgr', 1008 ,'user007', 'b', '2013-10-20 12:30:31' union all
select 'ad4545tr', 1009 ,'user001','a', '2013-10-20 12:30:35' union all
select 'wersdfdfg', 1000 ,'admin', 'b', '2013-10-20 12:30:39' union all
select 'tasdjuh', 1005 ,'admin', 'b', '2013-10-20 12:30:59'
go
select UID,工单号 ,工单处理人,工单类型,录入时间数据
from
(
select *,
ROW_NUMBER() over(partition by 工单处理人 order by 录入时间数据 desc) rownum
from tb
)t
where rownum = 1
/*
UID 工单号 工单处理人 工单类型 录入时间数据
tasdjuh 1005 admin b 2013-10-20 12:30:59.000
ad4545tr 1009 user001 a 2013-10-20 12:30:35.000
tysfdgr 1008 user007 b 2013-10-20 12:30:31.000
*/
drop table tb
go
create table tb(
UID varchar(20),工单号 int,工单处理人 varchar(20),
工单类型 varchar(20),
录入时间数据 datetime
)
insert into tb
select 'eadabyas', 1007 ,'admin', 'a', '2013-10-20 12:30:30' union all
select 'tysfdgr', 1008 ,'user007', 'b', '2013-10-20 12:30:31' union all
select 'ad4545tr', 1009 ,'user001','a', '2013-10-20 12:30:35' union all
select 'wersdfdfg', 1000 ,'admin', 'b', '2013-10-20 12:30:39' union all
select 'tasdjuh', 1005 ,'admin', 'b', '2013-10-20 12:30:59'
go
select UID,工单号 ,工单处理人,工单类型,录入时间数据
from
(
select *,
ROW_NUMBER() over(partition by 工单处理人 order by 录入时间数据 desc) rownum
from tb
)t
where rownum = 1
/*
UID 工单号 工单处理人 工单类型 录入时间数据
tasdjuh 1005 admin b 2013-10-20 12:30:59.000
ad4545tr 1009 user001 a 2013-10-20 12:30:35.000
tysfdgr 1008 user007 b 2013-10-20 12:30:31.000
*/