一个复杂的多表查询问题

Question

表是如下的表：
student 表: sid,name
course 表:cid,name
xuanke 表:id,sid,cid,score
要求查询出每门课程前三名的学生名字

Answer 1

select m.* from 
(
  select a.* , b.* , sum(c.score) score_all from student a, course b , xuanke c where a.sid = c.sid and b.cid = c.cid group by a.sid,a.name ,b.cid , b.name
) m where score_all in (
  select top 3 score_all from 
  (
    select a.* , b.* , sum(c.score) score_all from student a, course b , xuanke c where a.sid = c.sid and b.cid = c.cid group by a.sid,a.name ,b.cid , b.name
  ) n
  where n.cid = m.cid
)

Answer 2

上面少了个order by select m.* from 
(
  select a.* , b.* , sum(c.score) score_all from student a, course b , xuanke c where a.sid = c.sid and b.cid = c.cid group by a.sid,a.name ,b.cid , b.name
) m where score_all in (
  select top 3 score_all from 
  (
    select a.* , b.* , sum(c.score) score_all from student a, course b , xuanke c where a.sid = c.sid and b.cid = c.cid group by a.sid,a.name ,b.cid , b.name
  ) n
  where n.cid = m.cid order by score_all desc
)

Answer 3

楼上语句运行不过去，错误提示：
消息 8156，级别 16，状态 1，第 1 行
多次为 'm' 指定了列 'name'。
消息 8156，级别 16，状态 1，第 1 行
多次为 'n' 指定了列 'name'。我觉得只能通过游标把course表的课目取出来，然后将每个课目成绩的前三名插入到一张临时表中，最后把临时表的数据取出来。

Answer 4

select m.* 
from 
(
  select a.sid, a.name sname , b.cid,b.name cname , sum(c.score) score_all 
  from student a, course b , xuanke c 
  where a.sid = c.sid and b.cid = c.cid 
  group by a.sid,a.name ,b.cid , b.name
) m 
where score_all in 
(
  select top 3 score_all from 
  (
    select a.sid, a.name sname , b.cid,b.name cname , sum(c.score) score_all 
    from student a, course b , xuanke c 
    where a.sid = c.sid and b.cid = c.cid 
    group by a.sid,a.name ,b.cid , b.name
  ) n
  where n.cid = m.cid 
  order by score_all desc
)

Answer 5

select m.* from 
(   select s.sid,s.name,c.cid,c.name cname,x.score from xuanke x 
        inner join student s on x.sid =s.sid 
        inner join course c on c.cid =x.cid  
) m 
where score in 
( select top 3 score from 
  (select s.sid,s.name,c.cid,c.name cname,x.score from xuanke x 
        inner join student s on x.sid =s.sid 
        inner join course c on c.cid =x.cid 
  ) n
  where n.cid = m.cid 
  order by score desc
)

Answer 6

1楼 和 4楼  不错啊！  经过自己的修改总算是搞定了 你们的排序怎么都放里面啊我把它放到外面才起作用的。order by cname, score_all desc

Answer 7

说来看看还有 我有个地方不太明白啊  用in那里  用 top 3 有用呢 top 3 明明只返回了 最前面三条啊

Answer 8

select m.* 
from 
(
  select rn=ROW_NUMBER()OVER(PARTITION BY b.cid ORDER BY sum(c.score) DESC),a.sid, a.name sname , b.cid,b.name cname , sum(c.score) score_all 
  from student a, course b , xuanke c 
  where a.sid = c.sid and b.cid = c.cid 
  group by a.sid,a.name ,b.cid , b.name
) m 
WHERE rn<4

Answer 9

select m.Sname as 学生姓名, m.Cname as 课程名称, score_all as 成绩 from 
(   select s.sid, s.[name] as Sname, c.cid, c.[name] as Cname, x.score as score_all 
from xuanke x 
        inner join student s on x.sid =s.sid 
        inner join course c on c.cid =x.cid  
) m 
where score_all in 
( select top 3 score 
from (select c.cid, x.score 
from xuanke x 
        inner join student s on x.sid =s.sid 
        inner join course c on c.cid =x.cid 
) n
where n.cid = m.cid 
) order by cid, score_all desc
这个还可以简化吗      另外  为什么 top 3 里的数据明明是 最前面的三条 怎么可以分别显示每门前三的课程  能解释一下吗

Answer 10

where n.cid = m.cid 这个条件在起作用。

Answer 11

select m.Sname as 学生姓名, m.Cname as 课程名称, score_all as 成绩 
from 
(
  select rn = ROW_NUMBER() OVER(PARTITION BY b.cid ORDER BY c.score DESC),a.sid, a.name sname , b.cid,b.name cname , c.score score_all 
  from student a, course b , xuanke c 
  where a.sid = c.sid and b.cid = c.cid 
) m 
WHERE rn<4你上面写的代码  可以不用分组 也可以完成  我改了下  你分组是为什么防止什么吗
还有select rn = ROW_NUMBER() OVER(PARTITION BY …… 这里我还在 消化 还没懂是什么意思

Answer 12

--排名函数row_number() over(……) 返回一个分组并排序后的名次
--partition by b.cid 以b.cid进行分组
--并且以c.score排序我这样理解可以吗