一道面试题引发的数据库行列转换实践

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了一道面试题引发的数据库行列转换实践，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3945字，纯文字阅读大概需要6分钟。

内容图文

最近有个朋友去面试，问了我一道面试题。题目如下，在形如下面的数据库表score中，找出每门成绩（grade）都大于等于80分的学生姓名。

---------------------------------------- name 　　   | course  | grade ---------------------------------------- zhangsan  | Java　  | 70 ---------------------------------------- zhangsan  | C++　   | 80 ---------------------------------------- lisi 　   | Java　  | 90 ---------------------------------------- lisi 　   | C++ 　  | 60 ---------------------------------------- wangwu　  | Java　  | 85 ---------------------------------------- wangwu　  | C++　　 | 95 ----------------------------------------   期望结果 ---------------------------------------- name ---------------------------------------- wangwu ----------------------------------------   本文以MySQL数据库为例，以三种方案一步一步实现面试题要求。   方案一 1、寻求行列转换，也称矩阵转置，将多列数据转为一行，这一步最关键，实现SQL语句如下： 下面的sql是以score为主表来构建多列，构造的从表为每门课程的数据表，通过主表的 name 字段来关联从表的 name 字段。

select s.`name` as name,
(select grade from score where name = s.`name` and course = ‘Java‘)as ‘Java‘,
(select grade from score where name = s.`name` and course = ‘C++‘)as ‘C++‘
from score s

运行结果截图：

  2、对于1的执行结果，需要过滤掉重复的行，只要通过 distinct 关键字就可以了，实现SQL语句如下：

select distinct s.`name` as name,#此处加distinct来过滤相同的行
(select grade from score where name = s.`name` and course = ‘Java‘)as ‘Java‘,
(select grade from score where name = s.`name` and course = ‘C++‘)as ‘C++‘
from score s

运行结果截图：

3、最后通过构造一个子查询，即是把上面2的查询结果作为一个表来查询，进行 where 行级过滤就可以了，实现SQL语句如下：

select *
from
(
    select distinct s.`name` as name,#此处加distinct来过滤相同的行
    (select grade from score where name = s.`name` and course = ‘Java‘)as G1,
    (select grade from score where name = s.`name` and course = ‘C++‘)as G2
    from score s
) score
where G1>=80 and G2>=80

运行结果截图：

问题：这里有一个问题指出下，如果写成如下的sql语句，把Java和C++作为列名的话（还有C#），查询结果为NULL，这个问题后续会详解，请见运行结果截图：

select *
from
(
    select distinct s.`name` as name,#此处加distinct来过滤相同的行
    (select grade from score where name = s.`name` and course = ‘Java‘)as ‘Java‘,
    (select grade from score where name = s.`name` and course = ‘C++‘)as ‘C++‘
    from score s
) score
where ‘Java‘>=80 and ‘C++‘>=80

运行结果截图：

方案二

1、通过group和聚合函数sum的结合使用，通过group by name分组，利用sum假模假样计算出每门课程的成绩，sum的时候利用case判断课程类别，就得到了以行显示的每个学生的每门课程成绩，这一步最关键，实现SQL语句如下：

select  name,
sum(case when course=‘Java‘ then grade end) as ‘Java‘,
sum(case when course=‘C++‘ then grade end) as ‘C++‘
from score group by name

运行结果截图：

2、再通过构造一个子查询，即是把上面1的查询结果作为一个表来查询，进行 where 行级过滤就可以了，实现SQL语句如下：

select *
from
(
select  name,
sum(case when course=‘Java‘ then grade end) as G1,
sum(case when course=‘C++‘ then grade end) as G2
from score group by name
) score
where G1>=80 and G2>=80

运行结果截图：

方案三

1、先找出有任意课程<80分的学生，实现SQL语句如下：

select  name
from score
where grade<80

运行结果截图：

2、distinct出所有学生列表（不重复），实现SQL语句如下：

select distinct name
from score

运行结果截图：

3、通过构造子查询从查询2的结果排除出去查询1的结果，这一步骤有的数据库是有集合函数，比如SQL Server的Except，这儿我们用not exists进行行级过滤，实现SQL语句如下：

select *
from
(
    select distinct name
    from score
) score1
where not exists
(
    select *
    from
    (
        select distinct name
        from score
        where grade<80
    ) score2
    where score1.name=score2.name
)

运行结果截图：

总结：

一道面试题引发的数据库行列转换实践

标签：数据   src   alt   when   运行   聚合   code   大于等于   语句   

本文系统来源：http://www.cnblogs.com/chenlin/p/7412253.html

内容总结

以上是互联网集市为您收集整理的一道面试题引发的数据库行列转换实践全部内容，希望文章能够帮你解决一道面试题引发的数据库行列转换实践所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---