1标题这确实是一道真正的面试题，大家想想吧！如果不使用知识，它将丢失。太依赖各种框架和dll了，忘记了最基本的东西。不记得有多久没写SQL了。已知表信息如下：Department(depID,depName),depID系号,DepName系名Student(stuID,name,depID)学号,姓名,系号Score(stuID,category,score)学号,学科,gradesearch输出各系最高分，并按系号和学号升序排列，依次输出以下信息：系号、系名、学号、姓名、总分2实验USE[test]GO/******Object:Table[dbo].[Score]ScriptDate:05/11/201523:16:23******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[Score]([stuID][int]NOTNULL,[category][varchar](50)NOTNULL,[score][int]NOTNULL)ON[PRIMARY]GOSETANSI_PADDINGOFFGOINSERT[dbo].[Score]([stuID],[category],[score])VALUES(1,N'English',80)INSERT[dbo].[Score]([stuID],[category],[score])VALUES(2,N'Mathematics',80)INSERT[dbo].[Score]([stuID],[类别],[分数])VALUES(1,N'数学',70)INSERT[dbo].[Score]([stuID],[category],[score])VALUES(2,N'English',89)INSERT[dbo].[Score]([stuID],[category],[score])VALUES(3,N'English',81)INSERT[dbo].[Score]([stuID],[category],[score])VALUES(3,N'mathematics',71)INSERT[dbo].[Score]([stuID],[category],[score])VALUES(4,N'mathematics',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].[分数]([stuID],[类别],[分数])VALUES(6,N'数学',89)INSERT[dbo].[分数]([stuID],[类别],[分数])VALUES(7,N'数学',80)INSERT[dbo].[分数]([stuID],[类别],[分数])VALUES(8,N'数学',81)INSERT[dbo].[Score]([stuID],[category],[score])VALUES(9,N'数学',84)/******Object:Table[dbo].[部门]ScriptDate:05/11/201523:16:23******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[部门]([depID][int]IDENTITY(1,1)NOTNULL,[depName][varchar](50)NOTNULL,PRIMARYKEYCLUSTERED([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]GOSETANSI_PADDINGOFFGOSETIDENTITY_INSERT[dbo].[部门]ONINSERT[dbo].[部门]([depID],[depName])VALUES(1,N'计算机')INSERT[dbo].[部门]([depID],[depName])VALUES(2,N'生物')INSERT[dbo].[部门]([depID],[depName]])VALUES(3,N'数学')SETIDENTITY_INSERT[dbo].[Department]OFF/******Object:Table[dbo].[Student]ScriptDate:05/11/201523:16:23******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[Student]([stuID][int]IDENTITY(1,1)NOTNULL,[stuName][varchar](50)NOTNULL,[deptID][int]NOTNULL,PRIMARYKEYCLUSTERED([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]GOSETANSI_PADDINGOFFGOSETIDENTITY_INSERT[dbo].[学生]ONINSERT[dbo].[学生]]([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'生物幸运',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)SETIDENTITY_INSERT[dbo].[Student]OFF/******对象：默认[DF__Departmen__depNa__5441852A]脚本日期：05/11/201523:16:23******/ALTERTABLE[dbo].[部门]ADDDEFAULT('')FOR[depName]GO/******对象：默认[DF__Score__category__5EBF139D]ScriptDate：05/11/201523:16:23******/ALTERTABLE[dbo].[Score]ADDDEFAULT('')FOR[category]GO/******对象：默认[DF__Score__score__5FB337D6]ScriptDate：05/11/201523:16:23******/ALTERTABLE[dbo].[Score]ADDDEFAULT((0))FOR[score]GO/******对象：默认[DF__Student__stuName__59063A47]ScriptDate：05/11/201523:16:23******/ALTERTABLE[dbo].[Student]ADDDEFAULT('')FOR[stuName]GO/********Object:ForeignKey[FK__Student__deptID__59FA5E80]ScriptDate:05/11/201523:16:23******/ALTERTABLE[dbo].[Student]WITHCHECKADDFOREIGNKEY([deptID])REFERENCES[dbo].[Department]([depID])GO准备环境3结果面试的时候，没写出来。我当时就晕了，确实好久没写复杂的sql语句了。今天折腾了2-3个小时，终于试出来了。不知道有没有更好的写法？--SELECTDepartment.depID,Department.depName,Student.stuID,stuName,Dscore.scoresFROMDepartmentLEFTJOINStudentondepartment.depID=student.deptIDLEFTJOIN(SELECTScore.stuId,SUM(Score)AScoresFROMScoreGROUPbystuID)ASDscoreonStudent.stuIDdScore.stuID(从选择中选择最大值*(分数)AStopScoresFROMStudentLEFTJOIN(SELECTstuID,SUM(score)ASscoresFROMScoreGROUPBYstuID)ASnewScoreONStudent.stuID=newScore.stuIDgroupbydeptID)ASdepScorewhereDepartment.depID=depScore.deptIDandDscore.scores=depScore.topScores)orderbyDepartment.stuID;
