分组排序,取每组前五条记录

[xiangbin77]

--对表进行分组查询,在组内就进行排序,并且取得每组前5条记录. 还要获得每组使用其它条件查询时候的总记录数
--如:
--字段Key为int型.
select * from TableName where Word='FQ' and UserName='张三' group by Key order by ID desc
UNION ALL
select * from TableName where Word='FQ' and UserName='李四' group by Key order by ID desc
UNION ALL
select * from TableName where Word='FQ' and UserName='王五' group by Key order by ID desc
--这里只是分组了,我还要获得每组(条件Word='FQ')的记录总数
select count(ID) from TableName where Word='FQ'

---------------------------------------------------------
--上面的SQL是我的个人试想,实际运行是报错的.
-- 最后返回记录集.组程序中DataSet
----------------------------------------------------------

[-狙击手-]

--测试环境
create table student --学生列表
(
s_id int,
s_name char(10)
)
create  table class --课程列表
(
c_id int,
c_name char(10)
)

create table grade --学生成绩表
(
s_id int,
c_id int,
cj int
)

--测试数据
insert student values(1,'A')
insert student values(2,'B')
insert student values(3,'C')
insert student values(4,'D')
insert student values(5,'E')

insert class values(1,'历史')
insert class values(2,'数学')

insert grade values(1,1,60)
insert grade values(2,1,70)
insert grade values(3,1,80)
insert grade values(4,1,90)
insert grade values(5,1,100)
insert grade values(1,2,85)
insert grade values(2,2,67)
insert grade values(3,2,94)
insert grade values(4,2,63)
insert grade values(5,2,87)


select a.c_id,a.s_id,a.cj from grade a 
join grade b
on a.c_id = b.c_id 
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc


--处理重复分数
select a.c_id,a.s_id,a.cj 
from grade a join 
( 
  select c_id,cj
  from grade
  group by c_id,cj
) b
on a.c_id = b.c_id 
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc

--你可以以动态的修改: n  <= n 来获得
--每门课程的前n个最高分的


declare @ta table(n1 float,n2 float)
insert @ta values(2,3)
insert @ta values(8,5)
insert @ta values(8,5)
insert @ta values(9,3)
insert @ta values(3,1)
insert @ta values(10,4)

select n1,n2,cast((n2-n1)*100/n1 as decimal(10,2)) [(n2-n1)*100/n1] ,identity(int,1,1) id
into #temp
from @ta

select a.n1,a.n2,a.[(n2-n1)*100/n1],
count(case when a.[(n2-n1)*100/n1] <= b.[(n2-n1)*100/n1] 
      then 1 else null end) 名次
from #temp a cross join 
( 
  select [(n2-n1)*100/n1]
  from #temp
  group by [(n2-n1)*100/n1]
) b
group by a.n1,a.n2,a.[(n2-n1)*100/n1],a.id
order by 名次 

drop table #temp

n1      n2   (n2-n1)*100/n1 名次    
------ ----- -------------- --------
2.0    3.0   50.00          1
8.0    5.0   -37.50         2
8.0    5.0   -37.50         2
10.0   4.0   -60.00         3
9.0    3.0   -66.67         4
3.0    1.0   -66.67         4

（所影响的行数为 6 行）

[-狙击手-]

select a.UserName
from TableName a 
join TableName b 
on a.UserName = b.UserName
where a.Word='FQ'
group by a.UserName
having count(case when a.id<= b.idthen 1 else null end) < = 5 --可动态修改
order by a.UserName

[free1879]

group by Key？你要干什么？

[dawugui]

select * from tb t
where id in 
(
select top 5 id from tb where key = t.key order by id
)

[jinjazz]

如果是sql2005可以用 row_number() over(order by xxx)

[xiangbin77]

Key是字段名,不是关键词..可以把Key改成KeysID

[dawugui]

关键是这个不太明白:
还要获得每组使用其它条件查询时候的总记录数

[xiangbin77]

我是用的sql server 2000

[chengg0769]

先把这些分组数据插入到一个#temp的临时表，再count()
--#temp
select GID=0,* into #temp
from TableName
where 1=2

--insert group data
insert into #temp
select 1,* from TableName where Word='FQ' and UserName='张三' group by Key order by ID desc
UNION ALL
select 2,* from TableName where Word='FQ' and UserName='李四' group by Key order by ID desc
UNION ALL
select 3,* from TableName where Word='FQ' and UserName='王五' group by Key order by ID desc

--count
select GID,count(ID) from #temp where Word='FQ' group by GID


=====================================================
powerbuilder11&SQL QQ群请加:6539042

[dawugui]

select m.*,n.cnt from 
(
  select * from tb t where id in (select top 5 id from tb where Word='FQ' and [key] = t.[key] order by id)
) m,
(
  select [key] , cnt = count(ID) from tb where Word='FQ' group by [key]
) n
where m.[key] = n.[key]

[xiangbin77]

好像我写得还是不够明白..我重新开一个贴吧....这里回复了的我都给加,谢谢各位.

[pt1314917]

-------分组前5条记录
select * from TableName  a where Word='FQ' and id in(select top 5 id from TableName  where  Word='FQ' and UserName=a.UserName order by id desc)

---每组记录总数
select UserName,count(1)[总数] from TableName  where Word='FQ' group by  UserName

[dobear_0922]

select m.*,n.cnt 
from 
(
  select * from tb t where id in (select top 5 id from tb where Word='FQ' and [key] = t.[key] order by id)
) m join
(
  select [key] , cnt = count(ID) from tb where Word='FQ' group by [key]
) n on m.[key] = n.[key]