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

1 题目

这确实是一个真实的面试题，琢磨一下吧！知识不用，就会丢掉，我太依赖各种框架和dll了，已经忘记了最基本的东西。有多久没有写过SQL了，我已经不记得了。

[[133713]]

已知表信息如下：

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  准备环境

• 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.

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;

• 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.