.NET高级工程师面试题之SQL篇

　　1 题目
　　这确实是一个真实的面试题，琢磨一下吧！知识不用，会丢掉，我太依赖各种框架和dll了，已经忘记了基本的东西。有多久没有写过SQL了，我已经不记得了。
　　已知表信息如下：
　　Department(depID， depName)，depID 系编号，DepName系名
　　Student(stuID， name， depID) 学生编号，姓名，系编号
　　Score(stuID， category， score) 学生编码，科目，成绩
　　找出每一个系的高分，并且按系编号，学生编号升序排列，要求顺序输出以下信息：
　　系编号，系名，学生编号，姓名，总分
　　2 实验
　　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个小时，终于试出来了。不知道有没有更好的写法？
　　-- 每个系里的高分的学生信息
　　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