select top 5 * from (select 字段1,sum(字段2) from 表 group by 字段1)a
/***************************************************************/--创建有标识字段临时表 create table #temp( id int identity(1,1), name varchar(6) not null, col1 int not null, ... )--将数据插入临时表 insert into #temp(name,col1,...) select * from tab1 order by a--查询预期的结果集 select t0.* from #temp t0 inner join (select name,min(id) id from #temp group by name) t1 on t0.name= t1.name and (t0.id-t1.id)/5 = 0--删除临时表 drop table #temp
select * from ( select col1,sum(col2), (select count(*) from t where col1<=a.col1) id from t group by col1 ) ttt where id<=5
select * from ( select col1,sum(col2), (select count(*) from t where col1<=a.col1) id from t group by col1 ) ttt where id<=5 分组统计后为每组内的记录编号id(1、2、3....),然后取<=5的记录,明白?
create table #temp(
id int identity(1,1),
name varchar(6) not null,
col1 int not null,
...
)--将数据插入临时表
insert into #temp(name,col1,...) select * from tab1 order by a--查询预期的结果集
select t0.*
from #temp t0
inner join
(select name,min(id) id from #temp group by name) t1
on
t0.name= t1.name and (t0.id-t1.id)/5 = 0--删除临时表
drop table #temp
(
select col1,sum(col2),
(select count(*) from t where col1<=a.col1) id
from t group by col1
) ttt
where id<=5
(
select col1,sum(col2),
(select count(*) from t where col1<=a.col1) id
from t group by col1
) ttt
where id<=5
分组统计后为每组内的记录编号id(1、2、3....),然后取<=5的记录,明白?
1 1
2 1
3 1
4 1
5 1
6 1
7 2
8 2
9 2
10 3
11 3
比如这个表。我想取3个组中的前两条。
结果应该是这样的:
table: id type
1 1
2 1
7 2
8 2
10 2
11 2
能把这个查询语句写出来吗??麻烦了。
table: id type
1 1
2 1
7 2
8 2
10 3
11 3