首页 > 代码库 > [aaronyang原创] Mssql 一张表3列的sql面试题，看你sql学的怎么样

[aaronyang原创] Mssql 一张表3列的sql面试题，看你sql学的怎么样

2024-08-08 05:51:20   225人阅读

Student表的样子：

 

mssql demo

题目1：

考点：逻辑思维 筛选出学生中所有科目都大于60分的人， 例如：只要有一门低于60分，那么这个人的所有成绩都不要显示了，这里B的数学成绩只有13分，所以B的所有科目都不显示

显示的样子例如如下：

Image 3

如果你想自己尝试，你可以先不看下面的内容

我的思路：

一开始想到group，having，那就顺便复习下group和having的用法 我这样子写的，但是还是没有达到题目的要求

SELECT [StuName],MAX(StuSubject) AS ‘StuSubject‘,MAX(StuGrade) AS ‘StuGrade‘ FROM [dbo].[Student] GROUP BY StuName,StuGrade Having MIN(StuGrade) >= 60

效果：

Image 2

失败的原因是

所有科目，只要有一门低于60分，那么这个人都不要显示了，这里B的数学成绩只有13分，所以B的所有科目都不显示 这样子写跟  select * FROM Student WHERE StuGrade >= 60 有什么区别呢？ 所以想复杂了，换个思路，先找出所有低于60分的，由于可能一个人有多门成绩低于60分，那么stuname就会出现多次，所以还需要distinct,最终实现效果的sql如下：

 SELECT * FROM Student WHERE StuName not in(SELECT DISTINCT(StuName) FROM Student WHERE StuGrade < 60)

好了，说了这么多，因为长时间使用orm的后遗症，所以sql可能会忘记，本题目①考验思路，②复习group和having

拓展：例如不按照要求这样写sql

SELECT [StuName],StuGrade FROM [dbo].[Student] group by StuName having stugrade < 60

这样有个错误： HAVING 子句中的列 ‘dbo.Student.StuGrade‘ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中 所以having的用法，having是对group组中的数据再次进行筛选，但是筛选的组要在group by后面出现 关于group by

SELECT [StuName],StuGrade FROM [dbo].[Student] group by StuName

这样有个错误：选择列表中的列 ‘dbo.Student.StuGrade‘ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中 所以如果select 后面的列名称在group by中没有出现的时候，就要给该列加个聚合函数 提到聚合函数大家一定会首先想到最常用的：

1、 求个数：count 2、 求总和：sum 3、 求最大值：max 4、 求最小值：min 5、 求平均值：avg 聚合函数中有四个函数是我一直以来几乎就没有用到过的： 1、 求方差：var 2、 求总体方差：varp 3、 标准偏差：stdev 4、 求总体标准偏差：stdevp 除此以外Sql Server中还有几个集合函数： 1、 求校验和：checksum_agg 2、 求个数：count_big 3、 用于测试 cube 或 rollup 空值：grouping

接下来，我们加个WHERE条件，复习 WHERE GROUP Having混用W-G-H

SELECT [StuName],MAX(StuSubject) AS ‘StuSubject‘,MAX(StuGrade) AS ‘StuGrade‘ FROM [dbo].[Student] WHERE StuGrade>60 GROUP BY StuName,StuGrade Having MIN(StuGrade) >= 60

======================================本篇文章权限由AaronYang拥有，文章来自www.ayjs.net=======================================

题目2：

考点：sql的横竖转换 增加学生E的成绩

Image 4

我现在要显示的结果如下：

Image 8

  讲解如下：

① 决定group by列，这里只有StuName不重复，所以group by StuName

SELECT [StuName] FROM [dbo].[Student] GROUP BY StuName

②要被 case when的列，显示出来成绩，所以case 列名 when 成绩，然后技巧的加上其他列

MAX(CASE StuSubject WHEN ‘语文‘ THEN StuGrade ELSE -1 END) AS ‘语文成绩‘

③ 套用一个简单的sql公式，基本成型

SELECT [StuName], MAX(CASE StuSubject WHEN ‘语文‘ THEN StuGrade ELSE -1 END) AS ‘语文成绩‘, MAX(CASE StuSubject WHEN ‘数学‘ THEN StuGrade ELSE -1 END) AS ‘数学成绩‘, MAX(CASE StuSubject WHEN ‘英语‘ THEN StuGrade ELSE -1 END) AS ‘英语成绩‘ FROM [dbo].[Student] GROUP BY StuName

Image 5

④把-1变成 缺考 这里有简单的方式，但我还是用绕的方式，顺便复习几种sql的用法

1.  with 临时表的用法

with temp1(StuName,Yuwen,Shuxue,Yinyu) AS (SELECT [StuName], MAX(CASE StuSubject WHEN ‘语文‘ THEN StuGrade ELSE -1 END) AS ‘yuwen‘, MAX(CASE StuSubject WHEN ‘数学‘ THEN StuGrade ELSE -1 END) AS ‘shuxue‘, MAX(CASE StuSubject WHEN ‘英语‘ THEN StuGrade ELSE -1 END) AS ‘yingyu‘ FROM [dbo].[Student] GROUP BY StuName ) SELECT * FROM temp1
显示的效果跟上面的一样，但是复杂的列变成一列了，此时这张表已经在内存里了，所以不适合几百万条在内存条里了，企业内部开发应该可以。临时表用完了，会被释放掉，所以在select以后，你再select那种临时表，会报错了，因为不存在了OK，有了简单的表，接下来复习其他的

2. case when，CAST转换数据类型 我们基于临时表的基础来操作 上面的一种形式  case 列 when 值 then 结果  END ，这个相当于C#的switch用法 第二种形式是表达式 case when 条件 then 结果

with temp1(StuName,Yuwen,Shuxue,Yinyu) AS (SELECT [StuName], MAX(CASE StuSubject WHEN ‘语文‘ THEN StuGrade ELSE -1 END) AS ‘yuwen‘, MAX(CASE StuSubject WHEN ‘数学‘ THEN StuGrade ELSE -1 END) AS ‘shuxue‘, MAX(CASE StuSubject WHEN ‘英语‘ THEN StuGrade ELSE -1 END) AS ‘yingyu‘ FROM [dbo].[Student] GROUP BY StuName ) SELECT StuName, Yuwen=CASE WHEN Yuwen = -1 THEN ‘缺考‘ ELSE CAST(Yuwen AS varchar) END, Shuxue=CASE WHEN Shuxue = -1 THEN ‘缺考‘ ELSE CAST(Shuxue AS varchar) END, Yinyu=CASE WHEN Yinyu = -1 THEN ‘缺考‘ ELSE CAST(Yinyu AS varchar) END FROM temp1
显示结果：
Image 83. 拓展题目，增加一列自动增长列，说白了，也是考验你分页的前提  3.1 ROW_NUMBER() OVER(order by 要排序的列)
with temp1(StuName,Yuwen,Shuxue,Yinyu) AS (SELECT [StuName], MAX(CASE StuSubject WHEN ‘语文‘ THEN StuGrade ELSE -1 END) AS ‘yuwen‘, MAX(CASE StuSubject WHEN ‘数学‘ THEN StuGrade ELSE -1 END) AS ‘shuxue‘, MAX(CASE StuSubject WHEN ‘英语‘ THEN StuGrade ELSE -1 END) AS ‘yingyu‘ FROM [dbo].[Student] GROUP BY StuName ) SELECT ROW_NUMBER() OVER(order by StuName) as ID,StuName, Yuwen=CASE WHEN Yuwen = -1 THEN ‘缺考‘ ELSE CAST(Yuwen AS varchar) END, Shuxue=CASE WHEN Shuxue = -1 THEN ‘缺考‘ ELSE CAST(Shuxue AS varchar) END, Yinyu=CASE WHEN Yinyu = -1 THEN ‘缺考‘ ELSE CAST(Yinyu AS varchar) END FROM temp1
效果：Image 9  3.2 IDENTITY方法如果直接使用IDENTITY(int,1,1),会报错误如下：仅当 SELECT 语句中有 INTO 子句时，才能使用 IDENTITY 函数。用法：
with temp1(StuName,Yuwen,Shuxue,Yinyu) AS (SELECT [StuName], MAX(CASE StuSubject WHEN ‘语文‘ THEN StuGrade ELSE -1 END) AS ‘yuwen‘, MAX(CASE StuSubject WHEN ‘数学‘ THEN StuGrade ELSE -1 END) AS ‘shuxue‘, MAX(CASE StuSubject WHEN ‘英语‘ THEN StuGrade ELSE -1 END) AS ‘yingyu‘ FROM [dbo].[Student] GROUP BY StuName ) SELECT IDENTITY(int,1,1) as ID,StuName, Yuwen=CASE WHEN Yuwen = -1 THEN ‘缺考‘ ELSE CAST(Yuwen AS varchar) END, Shuxue=CASE WHEN Shuxue = -1 THEN ‘缺考‘ ELSE CAST(Shuxue AS varchar) END, Yinyu=CASE WHEN Yinyu = -1 THEN ‘缺考‘ ELSE CAST(Yinyu AS varchar) END  INTO #temp2 FROM temp1

 

后面要加个INTO 临时表名，其实此时，MSSQL已经将表存在了系统数据库-tempdb-临时表里面了

Image 10

所以接下来，你把上面的代码注释掉，都可以SELECT *　FROM #temp2了 那么怎样删除临时表，方法1，所有其他本地临时表在当前会话结束时都将被自动删除，不过不断开，再次执行，会报错，因为#temp2表已经存在了。 手动sql删除：

if exists(select * from tempdb..sysobjects where id=object_id(‘tempdb..#temp2‘)) drop table #temp2go

 

所以修改后的sql：

if exists(select * from tempdb..sysobjects where id=object_id(‘tempdb..#temp2‘)) drop table #temp2gowith temp1(StuName,Yuwen,Shuxue,Yinyu) AS (SELECT [StuName], MAX(CASE StuSubject WHEN ‘语文‘ THEN StuGrade ELSE -1 END) AS ‘yuwen‘, MAX(CASE StuSubject WHEN ‘数学‘ THEN StuGrade ELSE -1 END) AS ‘shuxue‘, MAX(CASE StuSubject WHEN ‘英语‘ THEN StuGrade ELSE -1 END) AS ‘yingyu‘ FROM [dbo].[Student] GROUP BY StuName ) SELECT IDENTITY(int,1,1) as ID,StuName, Yuwen=CASE WHEN Yuwen = -1 THEN ‘缺考‘ ELSE CAST(Yuwen AS varchar) END, Shuxue=CASE WHEN Shuxue = -1 THEN ‘缺考‘ ELSE CAST(Shuxue AS varchar) END, Yinyu=CASE WHEN Yinyu = -1 THEN ‘缺考‘ ELSE CAST(Yinyu AS varchar) END  INTO #temp2 FROM temp1SELECT * FROM #temp2GO

 

OK，这道题就啰嗦到这里了

======================================本篇文章权限由AaronYang拥有，文章来自www.ayjs.net=======================================

题目3：

考点： case when 我要显示的结果如下：

Image 11

提示： 90以上包括90优秀 80-90不包括90良好 60-80 及格 其他不及格   讲解思路： 我们只要基于with temp1的那个sql 在case when里面加几个条件判断就行了

with temp1(StuName,Yuwen,Shuxue,Yinyu) AS (SELECT [StuName], MAX(CASE StuSubject WHEN ‘语文‘ THEN StuGrade ELSE -1 END) AS ‘yuwen‘, MAX(CASE StuSubject WHEN ‘数学‘ THEN StuGrade ELSE -1 END) AS ‘shuxue‘, MAX(CASE StuSubject WHEN ‘英语‘ THEN StuGrade ELSE -1 END) AS ‘yingyu‘ FROM [dbo].[Student] GROUP BY StuName ) SELECT StuName, Yuwen=CASE WHEN Yuwen = -1 THEN ‘缺考‘ WHEN Yuwen >=90 THEN ‘优秀‘ WHEN Yuwen <90 AND Yuwen > =80THEN ‘良好‘ WHEN Yuwen <80 AND Yuwen > =60THEN ‘及格‘ ELSE ‘不及格‘ END,Shuxue=CASE WHEN Shuxue = -1 THEN ‘缺考‘ WHEN Shuxue >=90 THEN ‘优秀‘ WHEN Shuxue <90 AND Shuxue > =80THEN ‘良好‘ WHEN Shuxue <80 AND Shuxue > =60THEN ‘及格‘ ELSE ‘不及格‘ END, Yinyu=CASE WHEN Yinyu = -1 THEN ‘缺考‘ WHEN Yinyu >=90 THEN ‘优秀‘ WHEN Yinyu <90 AND Yinyu > =80THEN ‘良好‘ WHEN Yinyu <80 AND Yinyu > =60THEN ‘及格‘ ELSE ‘不及格‘ END FROM temp1

 

  ======================================本篇文章权限由AaronYang拥有，文章来自www.ayjs.net=======================================

题目4：

考点： 组中筛选top 要求：我要知道每个人在这次考试中他们考的最好的那个科目和分数，并按照姓名排序下 我要显示的结果如下：

Image 12

    答案：

SELECT * FROM Student t WHERE StuGrade=(SELECT MAX(StuGrade) FROM Student where StuName=t.StuName) ORDER BY StuName

恭喜你，已经看到这里了，不错！！Congratulation！

======================================本篇文章权限由AaronYang拥有，文章来自www.ayjs.net=======================================

[aaronyang原创] Mssql 一张表3列的sql面试题，看你sql学的怎么样


联系
我们