--原作: zjcxc(邹建)
--整理: 2004-05-08
--同一类取前面N条
select * from 表 aa
where id in (select top N id from 表 where 类别=aa.类别)
order by aa.类别
--整理: 2004-05-08
--同一类取前面N条
select * from 表 aa
where id in (select top N id from 表 where 类别=aa.类别)
order by aa.类别
where 主键 in(select top 10 主键 from t where t_time=a.t_time order by v_value desc)
select t_time,t_value
from ( select *,
c=( select count(*)
from T
where t_time=a.t_time and t_value>=a.t_value
)
from T a
) b
where c<=10
order by t_time,t_value desc
where 主键 in(select top 10 主键 from T where t_time=a.t_time order by v_value desc)
where a.t_time>='2004-06-01'
and a.t_time<='2004-06-07'
order by a.t_time
不知道为什么
select * from t a
where t_time in(select top 10 t_time from t where t_time=a.t_time order by v_value desc)则出现的数值组不止20行;(我暂时用2天的数值)中海 和 窗外 的我正在看,同时谢谢各位!
create table t (t_time datetime ,v_value int)
go
insert into t
select '2004-06-01',100
union all select '2004-06-01',200
union all select '2004-06-01',123
union all select '2004-06-01',234
union all select '2004-06-01',345
union all select '2004-06-01',200
union all select '2004-06-01',567
union all select '2004-06-01',678
union all select '2004-06-01',200
union all select '2004-06-01',789
union all select '2004-06-01',200
union all select '2004-06-01',200
union all select '2004-06-01',789
union all select '2004-06-01',200
union all select '2004-06-01',789
union all select '2004-06-01',200
union all select '2004-06-01',20787
union all select '2004-06-02',30
union all select '2004-06-02',123
union all select '2004-06-02',68
union all select '2004-06-02',67
union all select '2004-06-02',200
union all select '2004-06-02',567
union all select '2004-06-02',678
union all select '2004-06-02',200
union all select '2004-06-02',90
union all select '2004-06-02',200
union all select '2004-06-02',200
union all select '2004-06-02',789
union all select '2004-06-02',1246
union all select '2004-06-02',789
union all select '2004-06-02',20456790
union all select '2004-06-02',20787union all select '2004-06-03',39
union all select '2004-06-03',70
union all select '2004-06-03',156
union all select '2004-06-03',123
union all select '2004-06-03',68
union all select '2004-06-03',67
union all select '2004-06-03',200
union all select '2004-06-03',80
union all select '2004-06-03',678
union all select '2004-06-03',345
union all select '2004-06-03',90
union all select '2004-06-03',200
union all select '2004-06-03',678
union all select '2004-06-03',789
union all select '2004-06-03',1246
union all select '2004-06-03',123
union all select '2004-06-03',56
union all select '2004-06-03',89--生成临时表
select identity(int,1,1) as id ,* into #temp from t
--查询
select t_time ,v_value from #temp a
where id in(select top 10 id from #temp where t_time=a.t_time order by v_value desc)
order by t_time,v_value desc
--结果
t_time v_value
------------------------------------------------------ -----------
2004-06-01 00:00:00.000 20787
2004-06-01 00:00:00.000 789
2004-06-01 00:00:00.000 789
2004-06-01 00:00:00.000 789
2004-06-01 00:00:00.000 678
2004-06-01 00:00:00.000 567
2004-06-01 00:00:00.000 345
2004-06-01 00:00:00.000 234
2004-06-01 00:00:00.000 200
2004-06-01 00:00:00.000 200
2004-06-02 00:00:00.000 20456790
2004-06-02 00:00:00.000 20787
2004-06-02 00:00:00.000 1246
2004-06-02 00:00:00.000 789
2004-06-02 00:00:00.000 789
2004-06-02 00:00:00.000 678
2004-06-02 00:00:00.000 567
2004-06-02 00:00:00.000 200
2004-06-02 00:00:00.000 200
2004-06-02 00:00:00.000 200
2004-06-03 00:00:00.000 1246
2004-06-03 00:00:00.000 789
2004-06-03 00:00:00.000 678
2004-06-03 00:00:00.000 678
2004-06-03 00:00:00.000 345
2004-06-03 00:00:00.000 200
2004-06-03 00:00:00.000 200
2004-06-03 00:00:00.000 156
2004-06-03 00:00:00.000 123
2004-06-03 00:00:00.000 123(所影响的行数为 30 行)
显示的是全部的记录集,不知为什么,没想通~~~
where datediff(dd,t_time,a.t_time)=0 order by v_value desc)