专栏首页小数志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则简单的拼接,在确定不存在重复或无需去重的情况下其效率更高。

本文分享自微信公众号 - 小数志(Datazhi),作者:luanhz

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-06-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 行转列,列转行

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

    展菲
  • SQL 行转列,列转行

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

    李英杰同学
  • sql列转行

    --用于:交叉表的列数是不确定的 declare @sql varchar(8000)

    Java架构师必看
  • SQL 行转列

    如果你想熟练写各种统计报表的 SQL,那么行转列是你绕不开的一个点,你必须得掌握它。

    白日梦想家
  • SQL中PIVOT和UNPIVOT行列转换

    用户1112962
  • sql进阶 - 行转列

    用excel可以轻松实现行转列,用sql怎么实现呢?类似从图一转行成图二的功能:

    披头
  • sql行转列应用

    原始表 为了适应我们某个平台框架的使用(该框架直接配置sql,平台自动实现数据的返回。不写任何后端代码),同时方便前端不做任何处理。就采用sq...

    用户5166330
  • mysql行转列,列转行

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

    yaphetsfang
  • sql server 行转列 Pivot UnPivot

    PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现

    乔达摩@嘿
  • SQL中进行转列的几种方式

    在很多笔试的程序员中会有很多写SQL的情况,其中很多时候会考察行转列。那么这个时候如果能写出来几种行转列的SQL,会给面试官留下比较好的印象。

    海仔
  • SQL基本用法-行转列

    用户1112962
  • HAWQ中的行列转置

            行列转置是ETL或报表系统中的常见需求,HAWQ提供的内建函数和过程语言编程功能,使行列转置操作的实现变得更为简单。 一、行转列 1. 固定列数...

    用户1148526
  • SQL 行列转换简单示例

    SQLSERVER 2005 以后提供了新的方式进行行列转换,下面是一个实例供参考: if object_id('tb') is not null drop t...

    用户1177503
  • MySQL的行转列

    所谓的行转列操作,就是将一个表的行信息转化为列信息,说着可能比较笼统,这里先举个例子,如下:

    AsiaYe
  • mysql行转列转换

    皇上得了花柳病
  • sqlserver 行转列

    sqlserver把行转成列在我们编码中是经常遇到的我做一个小例子大家看一下 1 --创建一个表 2 create table PayPhoneMoney ...

    lpxxn
  • SQL Server 动态行转列(参数化表名、分组列、行转列字段、字段值)

    用户1112962
  • 数据库行转列的sql语句(zt)

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

    跟着阿笨一起玩NET
  • SQL 行转列+窗口函数的实例

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

    白日梦想家

扫码关注云+社区

领取腾讯云代金券