分组查询的问题?

[zhiguo2008]

有张学生成绩表:
create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2))
insert into cj select '张三','语文',98 union select '李四','语文',89 union select '王五','语文',67 union select '周攻','语文',56
union select '张三','数学',89 union select '李四','数学',78 union select '王五','数学',90 union select '周攻','数学',87

stuName KCM cj
---------- ---------- -------
李四 数学 78.00
李四 语文 89.00
王五 数学 90.00
王五 语文 67.00
张三 数学 89.00
张三 语文 98.00
周攻 数学 87.00
周攻 语文 56.00

求出每门课程成绩都排名该课程成绩前二名的学生,上面的结果应该是:
stuName
---------
张三

求SQL

[fcuandy]

create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2)) 
  insert into cj select '张三','语文',98 union select '李四','语文',89 union  select '王五','语文',67 union select '周攻','语文',56 
  union select '张三','数学',89 union select '李四','数学',78 union select '王五','数学',90 union select '周攻','数学',87 
go

select stuname from
	(select stuName,kcm,(select count(*) from cj where stuname!=a.stuname and kcm=a.kcm and cj>a.cj) cnt from cj a) x
	group by stuname having max(cnt)<=1

go

drop table cj
go

[chuifengde]

create table cj1(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2)) 
  insert into cj1 select '张三','语文',98 union select '李四','语文',89 union  select '王五','语文',67 union select '周攻','语文',56 
  union select '张三','数学',89 union select '李四','数学',78 union select '王五','数学',90 union select '周攻','数学',87 


SELECT stuname FROM cj1 a  
where cj IN(SELECT TOP 2 cj FROM cj1 WHERE kcm=a.kcm ORDER BY cj desc)
GROUP BY stuname HAVING(count(1)>1)

--result
/*stuname    
---------- 
张三

（所影响的行数为 1 行）*/

[fcuandy]

上面是聚合法，下面是exists

select distinct stuname from cj a
	where not exists(select kcm from cj b where a.stuname=stuname
				and (select count(*) from cj where kcm=b.kcm and stuname!=a.stuname and cj>b.cj)>1)

[jhwcd]

[Quote=引用 3 楼 fcuandy 的回复:]
上面是聚合法，下面是exists



SQL codeselect distinct stuname from cj a
where not exists(select kcm from cj b where a.stuname=stuname
and (select count(*) from cj where kcm=b.kcm and stuname!=a.stuname and cj>b.cj)>1)
[/Quote]
学习了。

[水族杰纶]

偷学并整理高人知识
if not object_id('cj')is null
drop table cj
go
create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2))
insert into cj select '张三','语文',98
union select '李四','语文',89
union select '王五','语文',67
union select '周攻','语文',56
union select '张三','数学',89
union select '李四','数学',78
union select '王五','数学',90
union select '周攻','数学',87
方法一：
select stuname from
(select stuName,kcm,(select count(*) from cj where stuname!=a.stuname and kcm=a.kcm and cj>a.cj) cnt from cj a) x
group by stuname having max(cnt)<=1
go
方法二：
SELECT stuname FROM cj1 a
where cj IN(SELECT TOP 2 cj FROM cj1 WHERE kcm=a.kcm ORDER BY cj desc)
GROUP BY stuname HAVING(count(1)>1)
方法三：
select distinct stuname from cj a
where not exists(select kcm from cj b where a.stuname=stuname
and (select count(*) from cj where kcm=b.kcm and stuname!=a.stuname and cj>b.cj)>1)

[hyqwan11112]

--这有个比较笨的方法，
  create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2)) 
  insert into cj select '张三','语文',98 union select '李四','语文',89 union  select '王五','语文',67 union select '周攻','语文',56 
  union select '张三','数学',89 union select '李四','数学',78 union select '王五','数学',90 union select '周攻','数学',87 
select stuName from 
(
select count(stuName) num,stuName from 
(select row_number() over(partition by KCM order by cj desc) rn,stuName,KCM from cj) tt
where rn = 1 or rn = 2 
group by stuName 
) tt
where num = 2

[hyqwan11112]

哦，忘记说了，只有2005能用！