当前位置:网站首页 / 数据库 / 正文

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

时间:2015年05月13日 | 作者 : aaronyang | 分类 : 数据库 | 浏览: 2736次 | 评论 1

Student表的样子:

 

522d5ed11894e118923.png

题目1:

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

显示的样子例如如下:

2.png

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

我的思路:

一开始想到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

效果:

522e996a35216fe2664.png

失败的原因

所有科目,只要有一门低于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的成绩

522d5ed11894e118923.png

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

5.png

  讲解如下:

① 决定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

6.png

④把-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

7.png

3. 拓展题目,增加一列自动增长列,说白了,也是考验你分页的前提

  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

  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-临时表里面了

9.png

所以接下来,你把上面的代码注释掉,都可以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 #temp2
 GO


 

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

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

题目3:

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

10.png

提示: 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 要求:我要知道每个人在这次考试中他们考的最好的那个科目和分数,并按照姓名排序下 我要显示的结果如下:

11.png

    答案:

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


恭喜你,已经看到这里了,不错!!Congratulation!

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

根据园友的回复,再拓展一些知识点

题目4:由 wy123 提供的 partition by思路

partition by用在OVER里面,跟GROUP BY 很类似,也能分组,但也有其他的作用。

例如 SQL:

SELECT ROW_NUMBER()OVER(PARTITION BY stuName ORDER BY StuGrade DESC ) AS rm, * FROM Student

效果:

我们使用 PARTITION BY 按照姓名(StuName)分组,然后每组中,按照成绩(StuGrade)降序,所以我们看到rm列变成了123,123,123等

接下来,就是取rm等于1的那些记录就可以了

SELECT * FROM(SELECT ROW_NUMBER()OVER(PARTITION BY stuName ORDER BY StuGrade DESC ) AS rm, * FROM Student
)t WHERE t.rm=1

题目2:行列转换,sqlserver2000的思路是case方式,sqlserver2005+以上,提供了Pivot(行转列)和Unpivot(列转行)

题目要求是行转列,这里简单使用下privot

结果集
PIVOT(

聚合函数(value_column)

FOR pivot_column

IN(<column_list>)

)

成型sql:

SELECT * FROM Student PIVOT(MAX(StuGrade) FOR StuSubject IN (语文,数学,英语)) A

效果:

接下来,我们把这个结果复制到一张新表Student2

首先就考虑到了 SELECT * INTO 新表 FROM (结果集) A

所以成型SQL:

SELECT StuName,语文 as Yuwen,数学 as Shuxue,英语 as Yingyu INTO Student2  FROM (    SELECT * FROM Student PIVOT(MAX(StuGrade) FOR StuSubject IN (语文,数学,英语)) A
  ) B

执行完以后

接下来我们使用 UnPivot,将列转换成行

SELECT StuName,StuSubject,StuGrade FROM
 (SELECT StuName,Yuwen,Shuxue,Yingyu FROM Student2) AS S2
 UNPIVOT(StuGrade FOR StuSubject IN 
      (Yuwen,Shuxue,Yingyu)
)AS unpvt

仔细对比一下,发现实现的有点不太理想,比如C的数学是缺考,好了,其实都是可以解决的啦

好了,就啰嗦到这里了,下次见

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

 

推荐您阅读更多有关于“MSSQL,”的文章

猜你喜欢

已有1位网友发表了看法:

1#朗朗他爹  2015-05-18 19:05:04 回复该评论
privot 这个最近刚好看过

发表评论

必填

选填

选填

必填

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

  查看权限

合肥科大智能常年招聘.NET,Java,Web前端,有想找想换工作的私聊我AY唯一QQ:875556003和AY交流

抖音号:wpfui,可以看到我的很多作品效果

AYUI8社区版Github地址:前往获取

作者:杨洋(AaronYang简称AY,安徽六安人)目前是个人,还没公司AY唯一QQ:875556003和AY交流

高中学历,2015年1月17日开始,兴趣学习研究WPF,目前工作繁忙,不接任何活

声明:AYUI7个人与商用免费,源码可购买。部分DEMO不免费.AY主要靠卖技术服务挣钱

不是从我处购买的ayui7源码,我不提供任何技术服务,如果你举报从哪里买的,我可以帮你转正为我的客户,并送demo

查看捐赠

AYUI7.X MVC教程 更新如下:

第一课 第二课 程序加密教程

vs2015 企业版密钥HM6NR-QXX7C-DFW2Y-8B82K-WTYJV

vs2017 企业版密钥NJVYC-BMHX2-G77MM-4XJMR-6Q8QF

标签列表