5 参考SQL
  正确的答案的结果是一样的,错误的各有各的不同,正确的答案后的性能也各有各的不同,不过呢,暂时没有水平去分析它,但是有空会把这些全部看一遍.谢谢各位啦!【2015-05-13 23:44】
  1、pursuer.chen
  SELECT B.depID,B.depName,B.stuID ,B.stuName,SUM(A.score )AS SUM_SCORE FROM Score A
  INNER JOIN
  (SELECT SA.depID,SA.depName,S.stuID,S.stuName FROM Student S
  INNER JOIN Score SE ON S.stuID=SE.stuID
  INNER JOIN (
  SELECT D.depID,D.depName ,MAX(SC.score )AS MX_score FROM Student S INNER JOIN Score SC ON S.stuID=SC.stuID INNER JOIN Department D ON S.deptID=D.depID
  GROUP BY D.depID,D.depName ) SA ON SE.score=SA.MX_score AND S.deptID=SA.depID )
  B ON A.stuID=B.stuID
  GROUP BY B.depID,B.depName,B.stuID ,B.stuName
  ORDER BY B.depID,B.stuID
  结果正确
  计算机    2    计算机李四    169
  生物    4    生物amy    152
  生物    5    生物kity    178
  数学    8    数学_haoxue    178
  2、Gamain 正确
  WITH cte1 as
  (
  SELECT
  DISTINCT
  D.depID,
  D.depName,
  S.stuID,
  S.stuName,
  SUM(Sc.score) OVER (PARTITION BY D.depID,S.stuID) as sumScore
  FROM Department D LEFT JOIN Student S ON D.depID=S.deptID
  LEFT JOIN Score Sc ON Sc.stuID=S.stuID
  ), cte2 as
  (
  SELECT
  DISTINCT
  depID,
  stuID,
  MAX(sumScore) OVER (PARTITION BY depID) as maxScore
  FROM
  cte1
  )
  SELECT
  c1.depID,
  c1.depName,
  c1.stuID,
  c1.stuName,
  c1.sumScore
  from cte2 c2 INNER JOIN cte1 c1
  ON c1.depID=c2.depID AND c1.stuID=c2.stuID and c1.sumScore=c2.maxScore;
  3、飞不动  正确
  use test;
  select
  e.*
  from
  (
  select c.depID,c.depName,a.stuID,b.stuName,a.total from
  (select stuID,sum(score) as total from Score group by stuID) a
  join Student b on b.stuID=a.stuID
  join Department c on c.depID=b.deptID
  ) e
  join
  (select b.deptID,max(a.total) maxScore from
  (select stuID,sum(score) as total from Score group by stuID) a
  join Student b on b.stuID=a.stuID
  group by b.deptID
  ) f on e.depID=f.deptID and e.total=f.MaxScore
  order by e.depID,e.stuID
  4、之路  错误
  select
  depID,
  depName,
  stuId,
  stuName,
  PerTotalScore
  from (
  select
  stuID,
  stuName,
  depID,
  depName,
  PerTotalScore,
  ROW_NUMBER() OVER(partition by depID order by PerTotalScore) as RowId
  from (
  select
  distinct
  s.stuID,
  s.stuName,
  d.depID,
  d.depName,
  SUM(c.score) OVER(partition by d.depID,s.stuID) as PerTotalScore
  from dbo.student s
  JOIN dbo.Department d on s.deptID=d.depID
  JOIN dbo.Score c ON s.StuID=c.StuID ) as T ) as TT
  WHERE TT.RowId=1
  order by depID,stuID
  计算机    1    计算机张三    150
  生物    4    生物amy    152
  数学    9    数学_wuyong    141
  5、King兵  正确
  WITH a
  AS
  (SELECT Department.depID, Department.depName, Student.stuID, stuName, Dscore.scores,ROW_NUMBER() OVER(PARTITION BY Department.depID ORDER BY scores DESC) ROWID
  FROM Department
  LEFT JOIN Student
  on department.depID = student.deptID
  LEFT JOIN (SELECT Score.stuId, SUM(Score) AS scores
  FROM Score
  GROUP by stuID
  ) AS Dscore
  on Student.stuID = dScore.stuID),
  b
  AS
  (
  SELECT Department.depID, Department.depName, Student.stuID, stuName, Dscore.scores,ROW_NUMBER() OVER(PARTITION BY Department.depID ORDER BY scores DESC) ROWID
  FROM Department
  LEFT JOIN Student
  on department.depID = student.deptID
  LEFT JOIN (SELECT Score.stuId, SUM(Score) AS scores
  FROM Score
  GROUP by stuID
  ) AS Dscore
  on Student.stuID = dScore.stuID
  )
  SELECT depID, depName, stuID, stuName, scores,ROWID FROM a WHERE a.scores = (SELECT MAX(scores) FROM b c WHERE a.depid = c.depid)
  6、 怪咖Eric  正确
  SELECT  bb.deptID ,
  cc.depName ,
  bb.stuID ,
  bb.stuName ,
  bb.TotalScore
  FROM    ( SELECT    * ,
  RANK() OVER ( PARTITION BY deptID ORDER BY TotalScore DESC ) AS pos
  FROM      ( SELECT    SUM(b.score) AS TotalScore ,
  a.stuID ,
  a.stuName ,
  a.deptID
  FROM      Student a
  JOIN Score b ON a.StuID = b.StuID
  GROUP BY  a.stuID ,
  a.stuName ,
  a.deptID
  ) aa
  ) bb
  JOIN dbo.Department cc ON bb.deptID = cc.depID
  JOIN dbo.Student dd ON bb.stuID = dd.stuID
  WHERE   pos = '1'
  ORDER BY bb.deptID ,
  bb.stuID
  7、Michael Jiang  手写 改后正确
  use test;
  SELECT D.*
  FROM (
  SELECT de.depID,
  de.depName,
  st.stuID,
  st.stuName,
  sc.score,
  RANK() OVER(
  PARTITION BY st.deptID
  ORDER BY sc.score DESC
  ) rowno
  FROM Student st
  LEFT JOIN Department de
  ON de.depID=st.deptID
  LEFT JOIN (
  SELECT sc.stuID,
  SUM(sc.score) score
  FROM Score sc
  GROUP BY sc.stuID
  ) sc
  ON sc.stuID=st.stuID
  ) D
  WHERE D.rowno = 1 --看错要求,原来只要列出高分
  ORDER BY D.depID, D.rowno
  8、正确 Li.zheng
  use test;
  select * from (
  select
  (select depName from Department where Department.depID = a.depID) as depName,
  (select stuName from Student where Student.stuID = a.stuID) as stuName,
  dense_rank() over(partition by depID order  by sumScore desc) as rank,
  a.sumScore
  from
  (
  select
  c.depID,b.stuid,sum(a.score) as sumScore
  from
  score as a
  inner join Student as b on a.stuid = b.stuid
  inner join Department as c on c.depID = b.deptID
  group by
  c.depID,b.stuid
  ) as a
  ) as b where b.rank = 1
  9、下个路口  错误 漏了并列第一
  SELECT *
  FROM   (
  SELECT s1.stuID,s1.stuName,s1.deptID,t.totalScore,d.depName,
  ROW_NUMBER() OVER(PARTITION BY d.depID ORDER BY totalScore DESC) AS
  Rn
  FROM   Student AS s1
  INNER JOIN (
  SELECT s.stuID,SUM(s2.score) AS totalScore FROM Student AS s
  INNER JOIN Department AS d ON  d.depID = s.deptID
  INNER JOIN Score s2 ON s2.stuID = s.stuID
  GROUP BY s.stuID
  ) AS t
  ON  t.stuID = s1.stuID
  INNER JOIN Department AS d
  ON  d.depID = s1.deptID
  ) result
  WHERE Rn = 1
  ORDER BY result.stuID
  9、自由_   正确
  select d.depID,d.depName,s.stuID,s.stuName,t.score from Department d left join
  (select s.stuID,sum(s.score) as score,st.deptID,
  rank() over(partition by st.deptID order by sum(s.score) desc) ra from Score s
  left join Student st on s.stuID = st.stuID group by s.stuID,st.deptID) t
  on d.depID = t.deptID left join Student s on t.stuID = s.stuID
  where t.ra = 1 order by d.depID,s.
  10、 手写 改了 之后 错误,
  use test;
  with Combin AS
  (
  SELECT MAX(score) AS 高分,deptID AS 系编号,MAX(a.stuID) AS 学生Id FROM Student a LEFT JOIN Score b ON a.stuID=b.stuID
  GROUP BY a.deptID
  )
  SELECT
  c.系编号,
  (SELECT depName FROM Department d WHERE d.depID=c.系编号 ) AS 系名,
  c.学生Id AS '学生编号',
  (SELECT stuName FROM Student e WHERE e.stuID=c.学生Id ) AS '姓名',
  c.高分
  FROM Combin c
  计算机    3    计算机王五    89
  生物    6    生物lucky    91
  数学    9    数学_wuyong    97
  11、 舍长   正确
  use test;
  WITH T1 AS (
  SELECT A.DEPID,A.DEPNAME,B.STUID,B.STUNAME,SUM(C.SCORE) AS TotalScore
  FROM Department A
  INNER JOIN Student B
  ON A.DEPID = B.DEPTID
  INNER JOIN Score C
  ON B.STUID = C.STUID
  GROUP BY A.DEPID,A.DEPNAME,B.STUID,B.STUNAME
  ),
  T2 AS (
  SELECT *,RANK() OVER(PARTITION BY DEPID ORDER BY TotalScore DESC) AS RankScore  FROM T1
  )
  SELECT * FROM T2 WHERE RankScore = 1 ORDER BY DEPID,STUID
  12、Ender.Lu   正确
  with
  tscore as (select stuID ,sum(score) as score from dbo.Score group by stuID),
  tinfo as (select Student.deptID ,Department.depName,dbo.Student.stuID,dbo.Student.stuName,tscore.score from dbo.Student
  inner join [dbo].[Department] on dbo.Department.depID = student.deptID
  left join tscore on tscore.stuid = Student.stuID),
  trank as (
  select deptID ,depName,stuID,stuName,score ,rank() over(partition by  deptID  order by score desc) as level from tinfo
  )
  select deptID ,depName,stuID,stuName,score from trank where level = 1 order by deptID ,stuID;
  13、McJeremy&Fan   正确
  select p.totalscore,p.stuid,p.stuname,p.deptid,x.depname from
  (
  select
  dense_rank() over(partition by deptid order by totalscore desc) as num,
  a.totalscore,b.stuid,b.stuname,b.deptid
  from
  (
  select stuid,sum(score) as totalscore from score
  group by stuid
  ) a inner join student b on a.stuid=b.stuid
  ) as p
  inner join department x on p.deptid=x.depid
  where p.num=1
  13、清水无大大鱼  正确
  with temp as(
  select a.deptid,a.stuID,a.stuName,b.score from student a,(select stuID,sum(score)as score from score group by stuID)b where a.stuID=b.stuID)
  select d.depID,d.depName,b.stuID,b.stuName,b.score from Department d,(
  select * from temp t where t.score=( select max(score) from temp sc where t.deptid=sc.deptid)) b where d.depID=b.deptID order by depID,stuID
  14、 BattleHeart  正确
  SELECT D.*,DD.depName FROM (
  SELECT C.stuID,
  C.TotleScore,
  C.stuName,
  C.deptID,
  DENSE_RANK() OVER(PARTITION BY C.deptID ORDER BY C.TotleScore DESC ) nubid
  FROM (SELECT S.stuID,
  ST.stuName,
  SUM(S.score) AS TotleScore,
  ST.deptID
  FROM dbo.Student AS ST
  INNER JOIN dbo.Score AS S ON S.stuID = ST.stuID
  GROUP BY S.stuID,ST.deptID,ST.stuName) AS C) AS D INNER JOIN dbo.Department AS DD
  ON DD.depID = D.deptID WHERE D.nubid=1