请教,用1条SQL语句能做出来否? 如果两级能,多级就不能一条语句 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 这种前序遍历以前写过,得想想~~~~ --生成测试数据create table #t(fileID int,level int,MainID int,wtime datetime) insert into #t select 2 ,1,2 ,'2005-05-29 00:00:00.000'insert into #t select 3 ,2,2 ,'2005-05-29 00:00:00.000'insert into #t select 4 ,1,4 ,'2005-05-29 00:00:00.000'insert into #t select 5 ,1,5 ,'2005-05-29 00:00:00.000'insert into #t select 6 ,1,6 ,'2005-05-29 09:37:00.000'insert into #t select 7 ,2,2 ,'2005-06-04 02:24:54.000'insert into #t select 8 ,1,8 ,'2005-06-04 02:57:38.000'insert into #t select 9 ,1,9 ,'2005-06-04 12:53:44.000'insert into #t select 10,1,10,'2005-06-04 12:56:26.000'insert into #t select 11,1,11,'2005-06-04 12:58:55.000'insert into #t select 12,1,12,'2005-06-04 12:59:07.000'insert into #t select 13,1,13,'2005-06-04 13:00:30.000'insert into #t select 14,1,14,'2005-06-04 13:01:28.000'insert into #t select 15,1,15,'2005-06-04 13:02:01.000'insert into #t select 16,1,16,'2005-06-04 13:35:37.000'insert into #t select 17,2,12,'2005-06-04 14:09:09.000'insert into #t select 18,1,18,'2005-06-04 15:34:45.000'insert into #t select 19,1,19,'2005-06-04 15:35:26.000'insert into #t select 20,1,20,'2005-06-04 16:08:16.000'insert into #t select 21,2,2 ,'2005-06-04 16:08:40.000'insert into #t select 22,2,2 ,'2005-06-04 16:25:17.000'--执行查询select * from #t where MainID in(select top 2 a.MainID from (select MainID,wtime = max(wtime) from #t group by MainID) a order by a.wtime desc) order by MainID--输出结果fileID level MainID wtime ------ ----- ------- ------------------------2 1 2 2005-05-29 00:00:00.0003 2 2 2005-05-29 00:00:00.0007 2 2 2005-06-04 02:24:54.00021 2 2 2005-06-04 16:08:40.00022 2 2 2005-06-04 16:25:17.00020 1 20 2005-06-04 16:08:16.000 SQL2000下编辑的DTS包能不能直接迁移到SQL2005上使用?如何操作? 怎样用Patindex定位一字符串中两相邻通配符的位置 求一简单的SQL语句,马上结贴! select查询值类型问题 头疼呀 1个礼拜了还没有解决 SQL Server 2005存储过程不同数据库间事务。 请教个选择数据库数据按excel要求填入表格的问题 SQL 2000中如何实现异地备份? 怎么玩SQL Server 2000下的Debug? 一个记录锁定的小问题。 在一个存储过程怎样写(统计).在线等 主从表查询?
create table #t(fileID int,level int,MainID int,wtime datetime)
insert into #t select 2 ,1,2 ,'2005-05-29 00:00:00.000'
insert into #t select 3 ,2,2 ,'2005-05-29 00:00:00.000'
insert into #t select 4 ,1,4 ,'2005-05-29 00:00:00.000'
insert into #t select 5 ,1,5 ,'2005-05-29 00:00:00.000'
insert into #t select 6 ,1,6 ,'2005-05-29 09:37:00.000'
insert into #t select 7 ,2,2 ,'2005-06-04 02:24:54.000'
insert into #t select 8 ,1,8 ,'2005-06-04 02:57:38.000'
insert into #t select 9 ,1,9 ,'2005-06-04 12:53:44.000'
insert into #t select 10,1,10,'2005-06-04 12:56:26.000'
insert into #t select 11,1,11,'2005-06-04 12:58:55.000'
insert into #t select 12,1,12,'2005-06-04 12:59:07.000'
insert into #t select 13,1,13,'2005-06-04 13:00:30.000'
insert into #t select 14,1,14,'2005-06-04 13:01:28.000'
insert into #t select 15,1,15,'2005-06-04 13:02:01.000'
insert into #t select 16,1,16,'2005-06-04 13:35:37.000'
insert into #t select 17,2,12,'2005-06-04 14:09:09.000'
insert into #t select 18,1,18,'2005-06-04 15:34:45.000'
insert into #t select 19,1,19,'2005-06-04 15:35:26.000'
insert into #t select 20,1,20,'2005-06-04 16:08:16.000'
insert into #t select 21,2,2 ,'2005-06-04 16:08:40.000'
insert into #t select 22,2,2 ,'2005-06-04 16:25:17.000'
--执行查询
select
*
from
#t
where
MainID in(select
top 2 a.MainID
from
(select
MainID,wtime = max(wtime)
from
#t
group by
MainID) a
order by a.wtime desc)
order by
MainID
--输出结果
fileID level MainID wtime
------ ----- ------- ------------------------
2 1 2 2005-05-29 00:00:00.000
3 2 2 2005-05-29 00:00:00.000
7 2 2 2005-06-04 02:24:54.000
21 2 2 2005-06-04 16:08:40.000
22 2 2 2005-06-04 16:25:17.000
20 1 20 2005-06-04 16:08:16.000