1 題目
這確實是一個真實的面試題，琢磨一下吧！知識不用，就會丟掉，我太依賴各種框架和dll了，已經忘記了最基本的東西。有多久沒有寫過SQL了，我已經不記得了。
已知表資訊如下：
Department(depID, depName),depID 系編號，DepName系名
Student(stuID, name, depID) 學生編號，姓名，系編號
Score(stuID, category, score) 學生編碼，科目，成績
找出每一個系的最高分，並且按系編號，學生編號升序排列，要求順序輸出以下資訊：
系編號，系名，學生編號，姓名，總分
2 實驗
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
USE [test] GO /****** Object: Table [dbo].[Score] Script Date: 05/11/2015 23:16:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Score]( [stuID] [int] NOT NULL, [category] [varchar](50) NOT NULL, [score] [int] NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (1, N'英語', 80) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (2, N'數學', 80) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (1, N'數學', 70) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (2, N'英語', 89) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (3, N'英語', 81) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (3, N'數學', 71) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (4, N'數學', 91) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (4, N'英語', 61) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (5, N'英語', 91) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (6, N'英語', 89) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (7, N'英語', 77) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (8, N'英語', 97) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (9, N'英語', 57) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (5, N'數學', 87) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (6, N'數學', 89) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (7, N'數學', 80) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (8, N'數學', 81) INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (9, N'數學', 84) /****** Object: Table [dbo].[Department] Script Date: 05/11/2015 23:16:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Department]( [depID] [int] IDENTITY(1,1) NOT NULL, [depName] [varchar](50) NOT NULL, PRIMARY KEY CLUSTERED ( [depID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[Department] ON INSERT [dbo].[Department] ([depID], [depName]) VALUES (1, N'計算機') INSERT [dbo].[Department] ([depID], [depName]) VALUES (2, N'生物') INSERT [dbo].[Department] ([depID], [depName]) VALUES (3, N'數學') SET IDENTITY_INSERT [dbo].[Department] OFF /****** Object: Table [dbo].[Student] Script Date: 05/11/2015 23:16:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Student]( [stuID] [int] IDENTITY(1,1) NOT NULL, [stuName] [varchar](50) NOT NULL, [deptID] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [stuID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[Student] ON INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (1, N'計算機張三', 1) INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (2, N'計算機李四', 1) INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (3, N'計算機王五', 1) INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (4, N'生物amy', 2) INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (5, N'生物kity', 2) INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (6, N'生物lucky', 2) INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (7, N'數學_yiming', 3) INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (8, N'數學_haoxue', 3) INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (9, N'數學_wuyong', 3) SET IDENTITY_INSERT [dbo].[Student] OFF /****** Object: Default [DF__Departmen__depNa__5441852A] Script Date: 05/11/2015 23:16:23 ******/ ALTER TABLE [dbo].[Department] ADD DEFAULT ('') FOR [depName] GO /****** Object: Default [DF__Score__category__5EBF139D] Script Date: 05/11/2015 23:16:23 ******/ ALTER TABLE [dbo].[Score] ADD DEFAULT ('') FOR [category] GO /****** Object: Default [DF__Score__score__5FB337D6] Script Date: 05/11/2015 23:16:23 ******/ ALTER TABLE [dbo].[Score] ADD DEFAULT ((0)) FOR [score] GO /****** Object: Default [DF__Student__stuName__59063A47] Script Date: 05/11/2015 23:16:23 ******/ ALTER TABLE [dbo].[Student] ADD DEFAULT ('') FOR [stuName] GO /****** Object: ForeignKey [FK__Student__deptID__59FA5E80] Script Date: 05/11/2015 23:16:23 ******/ ALTER TABLE [dbo].[Student] WITH CHECK ADD FOREIGN KEY([deptID]) REFERENCES [dbo].[Department] ([depID]) GO 準備環境 |
3 結果
面試的時候，沒有寫出來，當時腦袋昏沉沉的。也確實好久沒有寫複雜的sql語句了。今天花了2到3個小時，終於試出來了。不知道有沒有更好的寫法？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
-- 每個系裡的最高分的學生資訊 SELECT Department.depID, Department.depName, Student.stuID, stuName, Dscore.scores 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 where exists ( select * from ( SELECT deptID, MAX(scores) AS topScores FROM Student LEFT JOIN ( SELECT stuID,SUM(score) AS scores FROM Score GROUP BY stuID) AS newScore ON Student.stuID = newScore.stuID group by deptID) AS depScore where Department.depID = depScore.deptID and Dscore.scores=depScore.topScores ) order by Department.depID,Student.stuID; |
4 補充
看了那麼多的評論，自己寫的真的不咋樣，可惜今天沒有時間細細看了，現在還在公司加班！但百度一下的時間還是有滴，So整理一下相關資料先。
（1）、SQL2005四個排名函式（row_number、rank、dense_rank和ntile）的比較
（2）、關於with as：使用WITH AS提高效能簡化巢狀SQL
5 參考SQL
正確的答案的結果是一樣的，錯誤的各有各的不同，正確的答案後的效能也各有各的不同，不過呢，暫時沒有水平去分析它，但是有空會把這些全部看一遍.謝謝各位啦！【2015-05-13 23:44】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 |
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 |