专栏首页小数志SQL中的行转列和列转行

SQL中的行转列和列转行

导读

SQL是IT行业很多岗位都要求具备的一项能力,对于数据岗位而言更是如此,甚至说扎实的SQL基础也往往是入职这些岗位的必备技能。而在SQL面试中,一道出镜频率很高的题目就是行转列和列转行的问题,可以说这也是一道经典的SQL题目,本文就这一问题做以介绍分享。

给定如下模拟数据集,这也是SQL领域经典的学生成绩表问题。两张期望的数据表分别如下:

1)长表:

scoreLong

2)宽表:

scoreWide

考察的问题就是通过SQL语句实现在这两种形态间转换,其中长表转为宽表即行转列,宽表转为长表即列转行。

01 行转列:sum+if

在行转列中,经典的解决方案是条件聚合,即sum+if组合。其基本的思路是这样的:

  • 在长表的数据组织结构中,同一uid对应了多行,即每门课程一条记录,对应一组分数,而在宽表中需要将其变成同一uid下仅对应一行
  • 在长表中,仅有一列记录了课程成绩,但在宽表中则每门课作为一列记录成绩
  • 由多行变一行,那么直觉想到的就是要groupby聚合;由一列变多列,那么就涉及到衍生提取;
  • 既然要用groupby聚合,那么就涉及到将多门课的成绩汇总,但现在需要的不是所有成绩汇总,而仍然是各门课的独立成绩,所以需要用一个if函数加以筛选提取;当然,用case when也可以;
  • 在if筛选提取的基础上,针对不同课程设立不同的提取条件,并最终加一个聚合函数提取该列成绩即可。

按照这一思路,一句SQL实现行转列的写法如下:

SELECT uid,
      sum(if(course='语文', score, NULL)) as `语文`,
      sum(if(course='数学', score, NULL)) as `数学`,
      sum(if(course='英语', score, NULL)) as `英语`,
      sum(if(course='物理', score, NULL)) as `物理`,
      sum(if(course='化学', score, NULL)) as `化学`
FROM scoreLong
GROUP BY uid

查询结果当然是预期的行转列后的结果:

其中,if(course='语文', score, NULL)语句实现了当且仅当课程为语文时取值为课程成绩,否则取值为空,这相当于衍生了一个新的列字段,且对于每个uid而言,其所有成绩就只有特定课程的结果非空,其余均为空。这样,无论使用任何聚合函数,都可以得到该uid下指定课程的成绩结果。这里是用了sum函数,其实用min、max效果也是一样的,因为待聚合的数值中就只有那一个值非空。

02 列转行:union

列转行是上述过程的逆过程,所以其思路也比较直观:

  • 行记录由一行变为多行,列字段由多列变为单列;
  • 一行变多行需要复制,列字段由多列变单列相当于是堆积的过程,其实也可以看做是复制;
  • 一行变多行,那么复制的最直观实现当然是使用union,即分别针对每门课程提取一张衍生表,最后将所有课程的衍生表union到一起即可,其中需要注意字段的对齐

按照这一思路,给出SQL实现如下:

SELECT uid, '语文' as course, `语文` as score
FROM scoreWide
WHERE `语文` IS NOT NULL

UNION

SELECT uid, '数学' as course, `数学` as score
FROM scoreWide
WHERE `数学` IS NOT NULL

UNION

SELECT uid, '英语' as course, `英语` as score
FROM scoreWide
WHERE `英语` IS NOT NULL

UNION

SELECT uid, '物理' as course, `物理` as score
FROM scoreWide
WHERE `物理` IS NOT NULL

UNION

SELECT uid, '化学' as course, `化学` as score
FROM scoreWide
WHERE `化学` IS NOT NULL

查询结果当然是预期的长表。这里重点解释其中的三个细节:

  • 在每个单门课的衍生表中,例如这句:SELECT uid, '语文' as course, `语文` as score,用单引号包裹起来的课程名称是字符串常量,比如语文课的衍生表中的课程名都叫语文,然后将该列命名为course;第二个用反引号包裹起来的课程名实际上是从宽表中引用这一列的取值,然后将其命名为score。

这实际上对应的一个知识点是:在SQL中字符串的引用用单引号(其实双引号也可以),而列字段名称的引用则是用反引号

  • 上述用到了where条件过滤成绩为空值的记录,这实际是由于在原表中存在有空值的情况,如不加以过滤则在本例中最终查询记录有10条,其中两条记录的成绩字段为空
  • 最后,本例中用union关键字实现了多表的纵向拼接,实际上用union all更为合理,二者的区别是union会完成记录去重;而union all则简单的拼接,在确定不存在重复或无需去重的情况下其效率更高。
文章分享自微信公众号:
小数志

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!

作者:luanhz
原始发表时间:2021-06-12
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • SQL 中的行转列和列转行

    行转列,列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 的运算符PIVOT来实现。用传统...

    玖柒的小窝
  • SQL 行转列,列转行

    PIVOT 后跟一个聚合函数来拿到结果,FOR 后面跟的科目是我们要转换的列,这样的话科目中的语文、数学、英语就就被转换为列。IN 后面跟的就是具体的科目值。

    李英杰同学
  • SQL行转列、列转行

    这个主题还是比较常见的,行转列主要适用于对数据作聚合统计,如统计某类目的商品在某个时间区间的销售情况。列转行问题同样也很常见。

    全栈程序员站长
  • SQL 行转列,列转行

    PIVOT 后跟一个聚合函数来拿到结果,FOR 后面跟的科目是我们要转换的列,这样的话科目中的语文、数学、英语就就被转换为列。IN 后面跟的就是具体的科目值。

    网罗开发
  • oracle的行转列和列转行_sql中行转列

    --============================================== 作者:王运亮(wwwwgou) 时间:2011-06-10 博...

    Java架构师必看
  • mysql行转列,列转行

    行转列,列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 2005 新增的运算符PIVOT...

    yaphetsfang
  • mysql行转列、列转行

    发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/130537.html原文链接:https://javaforall.cn

    全栈程序员站长
  • mysql行转列简单例子_mysql行转列、列转行示例[通俗易懂]

    最近在开发过程中遇到问题,需要将数据库中一张表信息进行行转列操作,再将每列(即每个字段)作为与其他表进行联表查询的字段进行显示。

    全栈程序员站长
  • Kettle使用_27 行转列与列转行方法汇总

    大家好,我是架构君,一个会写代码吟诗的架构师。今天说一说Kettle使用_27 行转列与列转行方法汇总,希望能够帮助大家进步!!!

    Java架构师必看
  • Hive经典面试题之连续登录、行转列和列转行

    在日常工作进行数据的ETL或者面试时,经常遇到类似的问题,比如"统计连续N天交易额超过100万的店铺"、"统计连续登录天数超过3天的用户"等。对于这类问题,思路...

    大数据学习与分享
  • SQL Server 动态行转列(参数化表名、分组列、行转列字段、字段值)

    用户1112962
  • table行转列的sql详解

    table行转列的sql详解 tabele行转列的资料,网上搜一下很多。大家照着网上copy就可以实现自己想要的功能。但是大家在实现功能后是否想过行转列为什么要...

    我思故我在
  • oracle行转列、列转行、连续日期数字实现方式及mybatis下实现方式

    -- 行转列 SELECT * from ( SELECT tt1.SAP_ID,TT1.dt,TT1.EFF from ( SELECT t1.SAP...

    上帝
  • SQL 行转列+窗口函数的实例

    今天继续和大家分享 HackerRank 上的 SQL 编程挑战的解题思路,这一次的题目叫做“Occupations”,属于中等难度级别,答案提交的成功率在 9...

    白日梦想家
  • Spark中SQL列和并为一行

    但是在 spark 中没有 GROUP_CONCAT 命令,查找后发现命令 concat_ws :

    机器学习和大数据挖掘
  • 数据库行转列的sql语句(zt)

    转载:http://www.cnblogs.com/Charles2008/archive/2008/03/04/1090162.html

    跟着阿笨一起玩NET
  • 建议收藏丨sql行转列的一千种写法!!

                  那9个字,犹如一声惊雷,在这个热情的群里炸开了锅...

    不吃西红柿
  • jupyter 实现notebook中显示完整的行和列

    jupyter notebook中设置显示最大行和列及浮点数,在head观察行和列时不会省略

    砸漏
  • 【JavaP6大纲】MySQL篇:SQL的整个解析、执行过程原理、SQL行转列?

    sql语句执行顺序 (8) SELECT (9)DISTINCT<select_list> (1) FROM <left_table> (3) <join...

    Java廖志伟

扫码关注腾讯云开发者

领取腾讯云代金券