表结构及其数据如下:ID A1 time state
1 A 2011-09-21 1:15:00 上
2 A 2011-09-21 2:15:00 下
3 B 2011-09-21 1:50:00 上
4 B 2011-09-21 2:30:00 下
5 C 2011-09-21 3:15:00 上
6 C 2011-09-21 4:15:00 下
7 C 2011-09-21 5:15:00 中
8 A 2011-09-21 3:15:00 上
9 B 2011-09-21 4:15:00 下我想用一条查询语句查询出:以A1分组,时间最大的,这一行,如数据:A1 time state
C 2011-09-21 5:15:00 中
A 2011-09-21 3:15:00 上
B 2011-09-21 4:15:00 下
跪求各位,不知道有没有办法实现。
1 A 2011-09-21 1:15:00 上
2 A 2011-09-21 2:15:00 下
3 B 2011-09-21 1:50:00 上
4 B 2011-09-21 2:30:00 下
5 C 2011-09-21 3:15:00 上
6 C 2011-09-21 4:15:00 下
7 C 2011-09-21 5:15:00 中
8 A 2011-09-21 3:15:00 上
9 B 2011-09-21 4:15:00 下我想用一条查询语句查询出:以A1分组,时间最大的,这一行,如数据:A1 time state
C 2011-09-21 5:15:00 中
A 2011-09-21 3:15:00 上
B 2011-09-21 4:15:00 下
跪求各位,不知道有没有办法实现。
insert into tb select 1,'A','2011-09-21 1:15:00','上'
insert into tb select 2,'A','2011-09-21 2:15:00','下'
insert into tb select 3,'B','2011-09-21 1:50:00','上'
insert into tb select 4,'B','2011-09-21 2:30:00','下'
insert into tb select 5,'C','2011-09-21 3:15:00','上'
insert into tb select 6,'C','2011-09-21 4:15:00','下'
insert into tb select 7,'C','2011-09-21 5:15:00','中'
insert into tb select 8,'A','2011-09-21 3:15:00','上'
insert into tb select 9,'B','2011-09-21 4:15:00','下'
go
select * from tb a where not exists (select 1 from tb where a1=a.a1 and [time]>a.[time])
/*
ID A1 time state
----------- ---------- ----------------------- ----------
7 C 2011-09-21 05:15:00.000 中
8 A 2011-09-21 03:15:00.000 上
9 B 2011-09-21 04:15:00.000 下(3 行受影响)*/
go
drop table tb
create table #tb(id int, a1 varchar(2),time datetime,state varchar(2))
go
insert into #tb select 1, 'A', '2011-09-21 1:15:00', '上'
insert into #tb select 2, 'A', '2011-09-21 2:15:00', '下'
insert into #tb select 3, 'B', '2011-09-21 1:50:00', '上'
insert into #tb select 4, 'B', '2011-09-21 2:30:00', '下'
insert into #tb select 5, 'C', '2011-09-21 3:15:00', '上'
insert into #tb select 6, 'C', '2011-09-21 4:15:00', '下'
insert into #tb select 7, 'C', '2011-09-21 5:15:00', '中'
insert into #tb select 8, 'A', '2011-09-21 3:15:00', '上'
insert into #tb select 9, 'B', '2011-09-21 4:15:00', '下'
select a1,time,state from #tb t
where not exists(select 1 from #tb where a1=t.a1 and time>t.time)
------------
C 2011-09-21 05:15:00.000 中
A 2011-09-21 03:15:00.000 上
B 2011-09-21 04:15:00.000 下
go
insert into #tb select 1, 'A', '2011-09-21 1:15:00', '上'
insert into #tb select 2, 'A', '2011-09-21 2:15:00', '下'
insert into #tb select 3, 'B', '2011-09-21 1:50:00', '上'
insert into #tb select 4, 'B', '2011-09-21 2:30:00', '下'
insert into #tb select 5, 'C', '2011-09-21 3:15:00', '上'
insert into #tb select 6, 'C', '2011-09-21 4:15:00', '下'
insert into #tb select 7, 'C', '2011-09-21 5:15:00', '中'
insert into #tb select 8, 'A', '2011-09-21 3:15:00', '上'
insert into #tb select 9, 'B', '2011-09-21 4:15:00', '下'with cte as
(
select *,rowNum=ROW_NUMBER() over(partition by a1 order by time desc) from #tb
)
select * from cte where rowNum=1----------- ---- ----------------------- ----- --------------------
8 A 2011-09-21 03:15:00.000 上 1
9 B 2011-09-21 04:15:00.000 下 1
7 C 2011-09-21 05:15:00.000 中 1
select t.* from tb t where not exists (select 1 from tb where a1 = t.a1 and time > t.time) order by t.a1
create table #tb(id int, a1 varchar(2),time datetime,state varchar(2))
go
insert into #tb select 1, 'A', '2011-09-21 1:15:00', '上'
insert into #tb select 2, 'A', '2011-09-21 2:15:00', '下'
insert into #tb select 3, 'B', '2011-09-21 1:50:00', '上'
insert into #tb select 4, 'B', '2011-09-21 2:30:00', '下'
insert into #tb select 5, 'C', '2011-09-21 3:15:00', '上'
insert into #tb select 6, 'C', '2011-09-21 4:15:00', '下'
insert into #tb select 7, 'C', '2011-09-21 5:15:00', '中'
insert into #tb select 8, 'A', '2011-09-21 3:15:00', '上'
insert into #tb select 9, 'B', '2011-09-21 4:15:00', '下'
select a1,time,state from #tb t
where not exists(select 1 from #tb where a1=t.a1 and time>t.time)
------------
C 2011-09-21 05:15:00.000 中
A 2011-09-21 03:15:00.000 上
B 2011-09-21 04:15:00.000 下