关于数据筛选的问题
现有表A
ID SectionID StageID FinishTime
----------------------------------
1 1 1 2011-6-14
2 1 5 2011-6-16
3 1 3 2011-6-18
4 2 1 2011-6-13
5 2 2 2011-6-14
6 2 3 2011-6-15
7 1 4 2011-6-19
8 2 4 2011-6-17
想筛选得到如下查询结果:
ID SectionID StageID FinishTime
----------------------------------
7 1 4 2011-6-19
8 2 4 2011-6-17
即取完成时间最大的记录,并获取其对应的ID, StageID
ID是唯一的主键
请问查询语句该如何写?
注:开发环境SQL2000
ID SectionID StageID FinishTime
----------------------------------
1 1 1 2011-6-14
2 1 5 2011-6-16
3 1 3 2011-6-18
4 2 1 2011-6-13
5 2 2 2011-6-14
6 2 3 2011-6-15
7 1 4 2011-6-19
8 2 4 2011-6-17
想筛选得到如下查询结果:
ID SectionID StageID FinishTime
----------------------------------
7 1 4 2011-6-19
8 2 4 2011-6-17
即取完成时间最大的记录,并获取其对应的ID, StageID
ID是唯一的主键
请问查询语句该如何写?
注:开发环境SQL2000
作者: bimyboy 发布时间: 2011-06-14
SQL code
select t1.* from 表A t1 where not exists(select 1 from 表A t2 where t1.SectionID=t2.SectionID and t1.FinishTime<t2.FinishTime)
作者: X_0 发布时间: 2011-06-14
SQL code
declare @表A table (ID int,SectionID int,StageID int,FinishTime datetime) insert into @表A select 1,1,1,'2011-6-14' union all select 2,1,5,'2011-6-16' union all select 3,1,3,'2011-6-18' union all select 4,2,1,'2011-6-13' union all select 5,2,2,'2011-6-14' union all select 6,2,3,'2011-6-15' union all select 7,1,4,'2011-6-19' union all select 8,2,4,'2011-6-17' select ID,SectionID ,StageID,FinishTime= convert(varchar(10),FinishTime,120) from @表A a where FinishTime= (select max(FinishTime) from @表A where SectionID=a.SectionID) order by ID /* ID SectionID StageID FinishTime ----------- ----------- ----------- ---------- 7 1 4 2011-06-19 8 2 4 2011-06-17 */
作者: maco_wang 发布时间: 2011-06-14
SQL code
create table tb(ID int,SectionID int,StageID int,FinishTime datetime) insert into tb select 1,1,1,'2011-6-14' insert into tb select 2,1,5,'2011-6-16' insert into tb select 3,1,3,'2011-6-18' insert into tb select 4,2,1,'2011-6-13' insert into tb select 5,2,2,'2011-6-14' insert into tb select 6,2,3,'2011-6-15' insert into tb select 7,1,4,'2011-6-19' insert into tb select 8,2,4,'2011-6-17' go select * from tb a where not exists(select 1 from tb where SectionID=a.SectionID and FinishTime>a.FinishTime) go drop table tb /* ID SectionID StageID FinishTime ----------- ----------- ----------- ----------------------- 7 1 4 2011-06-19 00:00:00.000 8 2 4 2011-06-17 00:00:00.000 (2 行受影响) */
作者: qianjin036a 发布时间: 2011-06-14
SQL code
select * from tb t where not exists(select 1 from tb where SectionID = t.SectionID and FinishTime > t.FinishTime)
作者: AcHerat 发布时间: 2011-06-14
SQL code
select * from tb t where FinishTime =(select max(FinishTime ) from tb where SectionID = t.SectionID)
作者: fredrickhu 发布时间: 2011-06-14