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