.NET高级工程师面试题之SQL篇
2023-03-09 22:28:48 时间
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;
相关文章
- Windows10安装ElasticSearch笔记
- Windows 11 用户反馈无法安装 1 月更新,导致出现 0x800f0831 等错误
- 八个鲜为人知但很实用的Web API
- TypeScript 编译性能优化:Project Reference
- 什么是 Firefox ESR?如何在 Ubuntu 中安装它?
- 游戏实测显示 Windows 11 比 Ubuntu 更能发挥英伟达 RTX 3080/3090 显卡性能
- 已持续三周多时间,Windows 11 的 12 月更新让 AMD 用户头疼
- 宏碁发布 Aspire S 一体机和 Aspire 5/3 笔记本:搭载 Intel 第 12/13 代酷睿处理器
- 用于高效 Web 开发的十个 Visual Studio 代码扩展
- Nest 的实现原理?理解了 Reflect Metadata 就懂了
- Windows 11 和 Ubuntu 谁更能激发 AMD Radeon 7900XTX 显卡的性能?实测告诉你
- Windows 11 学院:如何禁用 Windows Update 通知
- Windows 11学院:如何启用本地安全机构(LSA)保护功能
- Windows 11 学院:如何启用本地安全机构(LSA)保护功能
- Windows 11 学院:如何启用 / 禁用固定应用到任务栏功能
- Golang网络编程Net包浅析
- Windows 11 学院:如何在稳定版 Windows 11 中使用截图工具的录屏功能
- 修复导致 Windows 10 / Windows 11 设备蓝屏问题,英特尔发布 Wi-Fi 驱动 22.190.0 更新
- Windows 11 预览版个性化新功能:允许用户在桌面上绘制贴纸
- Windows 11 学院:如何强制让 Windows 11 设备蓝屏