--try select * from 表 a where 时间 in (select top 2 时间 from 表 where convert(varchar(13),时间,120)=convert(varchar(13),a.时间,120))
表结构很简单,有这么几列: 人数、时间,现在要取每个小时最后两条数据,sql语句该怎么写?select * from t1 as t where (select count(*) from t1 where convert(varchar(13),时间,120) = convert(varchar(13),t.时间,120) and 时间 < t.时间) < 2
--try again select * from 表 a where 时间 in (select top 2 时间 from 表 where convert(varchar(13),时间,120)=convert(varchar(13),a.时间,120) order by 时间 desc)
create table Tb(人數 int,時間 datetime) insert into Tb select 10,'2007-9-7 14:32:00' insert into Tb select 20,'2007-9-7 14:33:00' insert into Tb select 30,'2007-9-7 14:34:00' insert into Tb select 40,'2007-9-7 14:35:00' insert into Tb select 50,'2007-9-7 15:32:00' insert into Tb select 60,'2007-9-7 15:33:00' insert into Tb select 70,'2007-9-7 15:34:00'GOselect * from Tb A where (select count(*) from Tb where datediff(hour,時間,A.時間)=0 and 時間>A.時間) <2 /* 人數 時間 ----------- ------------------------------------------------------ 30 2007-09-07 14:34:00.000 40 2007-09-07 14:35:00.000 60 2007-09-07 15:33:00.000 70 2007-09-07 15:34:00.000 */drop table tb
create table tab1(A varchar(2),B datetime) insert tab1 select 'A','2007-08-09 10:10:00' union all select 'A','2007-08-09 10:11:00' union all select 'A','2007-08-09 10:12:00' union all select 'A','2007-08-09 10:13:00' union all select 'A','2007-08-09 11:10:00' union all select 'A','2007-08-09 11:22:00' union all select 'A','2007-08-09 11:42:00' union all select 'A','2007-08-09 11:29:00' select * from tab1 A where (select count(*) from tab1 where datediff(hour,B,A.B)=0 and B>A.B) <2 drop table tab1
(8 行受影响) A B ---- ----------------------- A 2007-08-09 10:12:00.000 A 2007-08-09 10:13:00.000 A 2007-08-09 11:42:00.000 A 2007-08-09 11:29:00.000(4 行受影响)
select * from 表 a where 时间 in (select top 2 时间 from 表 where convert(varchar(13),时间,120)=convert(varchar(13),a.时间,120) order by 时间 desc)
select * from 表 a where 时间 in (select top 2 时间 from 表 where convert(varchar(13),时间,120)=convert(varchar(13),a.时间,120))
人数、时间,现在要取每个小时最后两条数据,sql语句该怎么写?select * from t1 as t
where (select count(*) from t1 where convert(varchar(13),时间,120) = convert(varchar(13),t.时间,120) and 时间 < t.时间) < 2
select * from 表 a where 时间 in (select top 2 时间 from 表 where convert(varchar(13),时间,120)=convert(varchar(13),a.时间,120) order by 时间 desc)
create table Tb(人數 int,時間 datetime)
insert into Tb select 10,'2007-9-7 14:32:00'
insert into Tb select 20,'2007-9-7 14:33:00'
insert into Tb select 30,'2007-9-7 14:34:00'
insert into Tb select 40,'2007-9-7 14:35:00'
insert into Tb select 50,'2007-9-7 15:32:00'
insert into Tb select 60,'2007-9-7 15:33:00'
insert into Tb select 70,'2007-9-7 15:34:00'GOselect *
from Tb A
where (select count(*) from Tb where datediff(hour,時間,A.時間)=0 and 時間>A.時間) <2
/*
人數 時間
----------- ------------------------------------------------------
30 2007-09-07 14:34:00.000
40 2007-09-07 14:35:00.000
60 2007-09-07 15:33:00.000
70 2007-09-07 15:34:00.000
*/drop table tb
insert tab1
select 'A','2007-08-09 10:10:00' union all
select 'A','2007-08-09 10:11:00' union all
select 'A','2007-08-09 10:12:00' union all
select 'A','2007-08-09 10:13:00' union all
select 'A','2007-08-09 11:10:00' union all
select 'A','2007-08-09 11:22:00' union all
select 'A','2007-08-09 11:42:00' union all
select 'A','2007-08-09 11:29:00'
select *
from tab1 A
where (select count(*) from tab1 where datediff(hour,B,A.B)=0 and B>A.B) <2
drop table tab1
(8 行受影响)
A B
---- -----------------------
A 2007-08-09 10:12:00.000
A 2007-08-09 10:13:00.000
A 2007-08-09 11:42:00.000
A 2007-08-09 11:29:00.000(4 行受影响)