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

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

1 题目

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

[[133713]]

已知表信息如下:

Department(depID, depName),depID 系编号,DepName系名

Student(stuID, name, depID) 学生编号,姓名,系编号

Score(stuID, category, score) 学生编码,科目,成绩

找出每一个系的最高分,并且按系编号,学生编号升序排列,要求顺序输出以下信息:

系编号,系名,学生编号,姓名,总分

2 实验

  1. USE [test] 
  2. GO 
  3. /****** Object:  Table [dbo].[Score]    Script Date: 05/11/2015 23:16:23 ******/ 
  4. SET ANSI_NULLS ON 
  5. GO 
  6. SET QUOTED_IDENTIFIER ON 
  7. GO 
  8. SET ANSI_PADDING ON 
  9. GO 
  10. CREATE TABLE [dbo].[Score]( 
  11.     [stuID] [int] NOT NULL, 
  12.     [category] [varchar](50) NOT NULL, 
  13.     [score] [int] NOT NULL 
  14. ) ON [PRIMARY] 
  15. GO 
  16. SET ANSI_PADDING OFF 
  17. GO 
  18. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (1, N'英语'80
  19. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (2, N'数学'80
  20. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (1, N'数学'70
  21. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (2, N'英语'89
  22. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (3, N'英语'81
  23. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (3, N'数学'71
  24. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (4, N'数学'91
  25. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (4, N'英语'61
  26. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (5, N'英语'91
  27. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (6, N'英语'89
  28. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (7, N'英语'77
  29. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (8, N'英语'97
  30. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (9, N'英语'57
  31. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (5, N'数学'87
  32. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (6, N'数学'89
  33. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (7, N'数学'80
  34. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (8, N'数学'81
  35. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (9, N'数学'84
  36. /****** Object:  Table [dbo].[Department]    Script Date: 05/11/2015 23:16:23 ******/ 
  37. SET ANSI_NULLS ON 
  38. GO 
  39. SET QUOTED_IDENTIFIER ON 
  40. GO 
  41. SET ANSI_PADDING ON 
  42. GO 
  43. CREATE TABLE [dbo].[Department]( 
  44.     [depID] [int] IDENTITY(1,1) NOT NULL, 
  45.     [depName] [varchar](50) NOT NULL, 
  46. PRIMARY KEY CLUSTERED  
  47.     [depID] ASC 
  48. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
  49. ) ON [PRIMARY] 
  50. GO 
  51. SET ANSI_PADDING OFF 
  52. GO 
  53. SET IDENTITY_INSERT [dbo].[Department] ON 
  54. INSERT [dbo].[Department] ([depID], [depName]) VALUES (1, N'计算机'
  55. INSERT [dbo].[Department] ([depID], [depName]) VALUES (2, N'生物'
  56. INSERT [dbo].[Department] ([depID], [depName]) VALUES (3, N'数学'
  57. SET IDENTITY_INSERT [dbo].[Department] OFF 
  58. /****** Object:  Table [dbo].[Student]    Script Date: 05/11/2015 23:16:23 ******/ 
  59. SET ANSI_NULLS ON 
  60. GO 
  61. SET QUOTED_IDENTIFIER ON 
  62. GO 
  63. SET ANSI_PADDING ON 
  64. GO 
  65. CREATE TABLE [dbo].[Student]( 
  66.     [stuID] [int] IDENTITY(1,1) NOT NULL, 
  67.     [stuName] [varchar](50) NOT NULL, 
  68.     [deptID] [int] NOT NULL, 
  69. PRIMARY KEY CLUSTERED  
  70.     [stuID] ASC 
  71. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
  72. ) ON [PRIMARY] 
  73. GO 
  74. SET ANSI_PADDING OFF 
  75. GO 
  76. SET IDENTITY_INSERT [dbo].[Student] ON 
  77. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (1, N'计算机张三'1
  78. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (2, N'计算机李四'1
  79. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (3, N'计算机王五'1
  80. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (4, N'生物amy'2
  81. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (5, N'生物kity'2
  82. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (6, N'生物lucky'2
  83. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (7, N'数学_yiming'3
  84. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (8, N'数学_haoxue'3
  85. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (9, N'数学_wuyong'3
  86. SET IDENTITY_INSERT [dbo].[Student] OFF 
  87. /****** Object:  Default [DF__Departmen__depNa__5441852A]    Script Date: 05/11/2015 23:16:23 ******/ 
  88. ALTER TABLE [dbo].[Department] ADD  DEFAULT ('') FOR [depName] 
  89. GO 
  90. /****** Object:  Default [DF__Score__category__5EBF139D]    Script Date: 05/11/2015 23:16:23 ******/ 
  91. ALTER TABLE [dbo].[Score] ADD  DEFAULT ('') FOR [category] 
  92. GO 
  93. /****** Object:  Default [DF__Score__score__5FB337D6]    Script Date: 05/11/2015 23:16:23 ******/ 
  94. ALTER TABLE [dbo].[Score] ADD  DEFAULT ((0)) FOR [score] 
  95. GO 
  96. /****** Object:  Default [DF__Student__stuName__59063A47]    Script Date: 05/11/2015 23:16:23 ******/ 
  97. ALTER TABLE [dbo].[Student] ADD  DEFAULT ('') FOR [stuName] 
  98. GO 
  99. /****** Object:  ForeignKey [FK__Student__deptID__59FA5E80]    Script Date: 05/11/2015 23:16:23 ******/ 
  100. ALTER TABLE [dbo].[Student]  WITH CHECK ADD FOREIGN KEY([deptID]) 
  101. REFERENCES [dbo].[Department] ([depID]) 
  102. GO 
  103.  
  104. 准备环境 

 

3 结果

面试的时候,没有写出来,当时脑袋昏沉沉的。也确实好久没有写复杂的sql语句了。今天花了2到3个小时,终于试出来了。不知道有没有更好的写法?

  1. -- 每个系里的最高分的学生信息 
  2. SELECT Department.depID, Department.depName, Student.stuID, stuName, Dscore.scores 
  3. FROM Department 
  4. LEFT JOIN Student 
  5. on department.depID = student.deptID 
  6. LEFT JOIN (SELECT Score.stuId, SUM(Score) AS scores  
  7.             FROM Score 
  8.             GROUP by stuID 
  9. ) AS Dscore 
  10. on Student.stuID = dScore.stuID 
  11. where exists    (     
  12. select * 
  13. from 
  14. (         
  15.  SELECT deptID, MAX(scores) AS topScores 
  16.  FROM Student 
  17.  LEFT JOIN  
  18.         ( 
  19.  SELECT stuID,SUM(score) AS scores 
  20.  FROM Score 
  21.  GROUP BY stuID) AS newScore 
  22.  ON Student.stuID = newScore.stuID 
  23.  group by deptID) AS depScore 
  24.  where Department.depID = depScore.deptID and Dscore.scores=depScore.topScores 
  25.  ) 
  26.  order by Department.depID,Student.stuID; 

 

责任编辑:王雪燕 来源: 博客园
相关推荐

2020-12-18 11:55:27

编程面试

2022-08-23 08:00:00

高级工程师软件工程师代码库

2020-04-03 09:35:33

前端框架Vue

2023-05-29 16:09:22

JavaScript技能浏览器

2018-09-20 10:55:38

数据库顺丰高级工程师

2018-04-16 12:38:37

大数据工程师面试

2009-09-21 15:27:17

2009-09-08 17:50:01

2009-09-24 15:16:04

CCNA网络工程师面试

2009-08-18 10:30:32

2020-03-17 15:15:49

Python工程师面试题

2009-09-24 15:26:26

2023-08-11 13:25:00

JavaScript

2010-12-24 10:47:48

网络规划设计师

2010-08-12 16:42:11

网络工程师

2011-01-04 11:48:04

系统分析师

2010-12-24 10:50:43

系统架构设计师

2012-04-23 09:21:11

NetflixAmazonQCon

2010-12-29 11:15:51

信息系统项目管理师

2011-03-24 13:27:37

SQL
点赞
收藏

51CTO技术栈公众号