.NET高级工程师面试题之SQL篇-阿里云开发者社区

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

2017-08-01 2174

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介：

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;

【编辑推荐】

作者：岁月如初

来源：51CTO

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景