????4 ????
??????????????????????д??????????????????????????????????????????????μ???仹???еΣ?So?????????????????
??????1????SQL2005?????????????row_number??rank??dense_rank??ntile??????
??????2????????with as?????WITH AS???????????SQL
????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