前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >SQL 行转列+窗口函数的实例

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

作者头像
白日梦想家
发布2020-08-02 17:08:40
2K0
发布2020-08-02 17:08:40
举报
文章被收录于专栏:SQL实现

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

问题描述

有一个职业表(OCCUPATIONS ),它有两个字段:Name、Occupation。其中,Occupation 的值只有四种:Doctor、Professor、Singer 和 Actor 。

OCCUPATIONS 表的表结构及数据如下:

Column

Type

Name

String

Occupation

String

OCCUPATIONS 的表结构

Name

Occupation

Samantha

Doctor

Julia

Actor

Maria

Actor

Meera

Singer

Ashley

Professor

Ketty

Professor

Christeen

Professor

Jane

Actor

Jenny

Doctor

Priya

Singer

OCCUPATIONS 的表数据

要求:

  1. 将数据按照 Doctor、Professor、Singer 、Actor 的顺序分成四列输出;
  2. 每一列的数据从上到下按姓名的字母顺序升序排序;
  3. 人员不足的职位列上用 NULL 填充。

输出结果:

代码语言:javascript
复制
Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

解决方案

一般遇到“将一列中的所有唯一值作为新的列名输出”这种需求,我们首先想到的就应该是行转列,我之前写过一篇介绍SQL 行转列的通用实现的文章,感兴趣的朋友点进来看看。

做行转列时分组的依据是什么呢?即依据哪个字段分组。答案是依据每个职位中姓名的排序序号作为分组条件,而每个职位里面姓名的出现的序号可通过窗口函数求得。

先来看看对职业为 “Actor” 和 “Doctor” 的数据做组内排序。

代码语言:javascript
复制
SELECT
  occupation as p,
  row_number () over (
    PARTITION BY occupation
ORDER BY name
) AS rn,
name
FROM
  OCCUPATIONS
WHERE
  occupation IN ('Actor','Doctor')

排序的结果:

代码语言:javascript
复制
p      rn name
------ -- --------
Actor  1  Eve
Actor  2  Jennifer
Actor  3  Ketty
Actor  4  Samantha
Doctor 1  Aamina
Doctor 2  Julia
Doctor 3  Priya

最后,我们把窗口函数和行转列结合起来,就可以实现题目所要求的输出。

代码语言:javascript
复制
WITH t AS
(SELECT
  occupation,
  row_number () over (
    PARTITION BY occupation
ORDER BY NAME
) AS rn,
NAME
FROM
  OCCUPATIONS)
SELECT
  MAX(
    IF(occupation = 'Doctor', NAME, NULL)
  ) AS Doctor,
  MAX(
    IF(
      occupation = 'Professor',
      NAME,
      NULL
    )
  ) AS Professor,
  MAX(
    IF(occupation = 'Singer', NAME, NULL)
  ) AS Singer,
  MAX(IF(occupation = 'Actor', NAME, NULL)) AS Actor
FROM
  t
GROUP BY rn

如果你的 MySQL 数据库的版本在 8.0 之前,那么可以用用户变量替代窗口函数实现组内排序的功能。

代码语言:javascript
复制
SELECT
  MAX(
    IF(occupation = 'Doctor', NAME, NULL)
  ) AS Doctor,
  MAX(
    IF(
      occupation = 'Professor',
      NAME,
      NULL
    )
  ) AS Professor,
  MAX(
    IF(occupation = 'Singer', NAME, NULL)
  ) AS Singer,
  MAX(IF(occupation = 'Actor', NAME, NULL)) AS Actor
FROM
  (SELECT
    NAME,
    occupation,
    @rn := IF(@oc = occupation, @rn + 1, 1) AS seq,
    @oc := occupation
  FROM
    occupations a,
    (SELECT
      @rn := 0,
      @oc := NULL) b
  ORDER BY occupation,name
  ) t
  GROUP BY seq
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-07-31,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 SQL实现 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

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

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 问题描述
  • 解决方案
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档