MySQL 给两次转置的结果集增加列头

最新推荐文章于 2024-03-18 17:59:28 发布
最新推荐文章于 2024-03-18 17:59:28 发布
阅读量599 收藏 1
点赞数 1
分类专栏： MySQL从小工到专家之路 # MySQL CookBook 文章标签： mysql 面试 转置 多次转置 增加列头
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010520724/article/details/114310170
版权

备注:测试数据库版本为MySQL 8.0

文章目录

测试数据:

create table it_research(deptno int, ename varchar(20));

insert into it_research values (100,'HOPKINS');
insert into it_research values (100,'JONES');
insert into it_research values (100,'TONEY');
insert into it_research values (200,'MORALES');
insert into it_research values (200,'P.WHITAKER');
insert into it_research values (200,'MARCIANO');
insert into it_research values (200,'ROBINSON');
insert into it_research values (300,'LACY');
insert into it_research values (300,'WRIGHT');
insert into it_research values (300,'J.TAYLOR');


CREATE TABLE IT_APPS (deptno int,ename varchar(20));

insert into it_apps values (400,'CORRALES');
insert into it_apps values (400,'MAYWEATHER');
insert into it_apps values (400,'CASTILLO');
insert into it_apps values (400,'MARQUEZ');
insert into it_apps values (400,'MOSLEY');
insert into it_apps values (500,'GATTI');
insert into it_apps values (500,'CALZAGHE');
insert into it_apps values (600,'LAMOTTA');
insert into it_apps values (600,'HAGLER');
insert into it_apps values (600,'HEARNS');
insert into it_apps values (600,'FRAZIER');
insert into it_apps values (700,'GUINN');
insert into it_apps values (700,'JUDAH');
insert into it_apps values (700,'MARGARITO');

一.需求

把两个结果集叠在一起，然后把他们转置为两列，另外，还要为每列加一个"标题"。
例如，有两个表，它们包含公司中有关员工的信息，这些员工在不同地区从事开发工作(也即研究和应用):

mysql> select * from it_research;
±-------±-----------+
| deptno | ename |
±-------±-----------+
| 100 | HOPKINS |
| 100 | JONES |
| 100 | TONEY |
| 200 | MORALES |
| 200 | P.WHITAKER |
| 200 | MARCIANO |
| 200 | ROBINSON |
| 300 | LACY |
| 300 | WRIGHT |
| 300 | J.TAYLOR |
±-------±-----------+
10 rows in set (0.00 sec)

mysql> select * from it_apps;
±-------±-----------+
| deptno | ename |
±-------±-----------+
| 400 | CORRALES |
| 400 | MAYWEATHER |
| 400 | CASTILLO |
| 400 | MARQUEZ |
| 400 | MOSLEY |
| 500 | GATTI |
| 500 | CALZAGHE |
| 600 | LAMOTTA |
| 600 | HAGLER |
| 600 | HEARNS |
| 600 | FRAZIER |
| 700 | GUINN |
| 700 | JUDAH |
| 700 | MARGARITO |
±-------±-----------+
14 rows in set (0.00 sec)

要创建一个报表，它分两栏列出两个表中的员工。
对于每一列，都返回deptno和ename。

最后，返回下列结果集:
image.png

二.解决方案

本解决方案只需要一个简单的堆叠及转置(合并后转置)并且再"拧"一次:deptno 一定在每个员工的ename之前。
这里的技巧采用了笛卡尔积为每个deptno生产附加行，这样才有足够的行显示所有员工和deptno。

with tmp1 as
(
select deptno,
       ename,
       count(*) over (partition by deptno) cnt
from it_apps
order by deptno,ename 
),
tmp2 as
(
select 1 id union select 2 
),
tmp3 as
(
select deptno,
       ename,
       cnt,
       row_number() over w1 as 'rn'
  from tmp1,tmp2
window w1 as (partition by deptno order by id,ename)
),
tmp4 as
(
select 1 flag1,
       1 flag2,
       case when rn = 1 then deptno else concat('  ',ename) end it_dept
  from tmp3 
  where rn <= cnt + 1
)
,
tmp5 as
(
select deptno,
       ename,
       count(*) over (partition by deptno) cnt
from it_research
order by deptno,ename 
),
tmp6 as
(
select deptno,
       ename,
       cnt,
       row_number() over w2 as 'rn'
  from tmp5,tmp2
 window w2 as (partition by deptno order by id,ename)
),
tmp7 as
(
select 1 flag1,0 flag2,case when rn = 1 then deptno else concat('  ',ename) end it_dept
  from tmp6
  where rn <= cnt + 1
),
tmp8 as
(
select flag1,
       flag2,
       it_dept
 from tmp7
union all
select flag1,
       flag2,
       it_dept
 from tmp4
),
tmp9 AS
(
select flag1,
       flag2,
       it_dept,
       row_number() over w3 as 'rn2'
  from tmp8
 window w3 as ()
),
tmp10 as
(
select sum(flag1) over (partition by flag2 order by flag1,rn2) flag,
       flag2,
       it_dept
  from tmp9
)
select max(case when flag2 = 0 then it_dept end) research,
       max(case when flag2 = 1 then it_dept end) apps
  from tmp10
 group by flag
 order by flag

测试记录:

mysql> with tmp1 as
    -> (
    -> select deptno,
    ->        ename,
    ->        count(*) over (partition by deptno) cnt
    -> from it_apps
    -> order by deptno,ename
    -> ),
    -> tmp2 as
    -> (
    -> select 1 id union select 2
    -> ),
    -> tmp3 as
    -> (
    -> select deptno,
    ->        ename,
    ->        cnt,
    ->        row_number() over w1 as 'rn'
    ->   from tmp1,tmp2
    -> window w1 as (partition by deptno order by id,ename)
    -> ),
    -> tmp4 as
    -> (
    -> select 1 flag1,
    ->        1 flag2,
    ->        case when rn = 1 then deptno else concat('  ',ename) end it_dept
    ->   from tmp3
    ->   where rn <= cnt + 1
    -> )
    -> ,
    -> tmp5 as
    -> (
    -> select deptno,
    ->        ename,
    ->        count(*) over (partition by deptno) cnt
    -> from it_research
    -> order by deptno,ename
    -> ),
    -> tmp6 as
    -> (
    -> select deptno,
    ->        ename,
    ->        cnt,
    ->        row_number() over w2 as 'rn'
    ->   from tmp5,tmp2
    ->  window w2 as (partition by deptno order by id,ename)
    -> ),
    -> tmp7 as
    -> (
    -> select 1 flag1,0 flag2,case when rn = 1 then deptno else concat('  ',ename) end it_dept
    ->   from tmp6
    ->   where rn <= cnt + 1
    -> ),
    -> tmp8 as
    -> (
    -> select flag1,
    ->        flag2,
    ->        it_dept
    ->  from tmp7
    -> union all
    -> select flag1,
    ->        flag2,
    ->        it_dept
    ->  from tmp4
    -> ),
    -> tmp9 AS
    -> (
    -> select flag1,
    ->        flag2,
    ->        it_dept,
    ->        row_number() over w3 as 'rn2'
    ->   from tmp8
    ->  window w3 as ()
    -> ),
    -> tmp10 as
    -> (
    -> select sum(flag1) over (partition by flag2 order by flag1,rn2) flag,
    ->        flag2,
    ->        it_dept
    ->   from tmp9
    -> )
    -> select max(case when flag2 = 0 then it_dept end) research,
    ->        max(case when flag2 = 1 then it_dept end) apps
    ->   from tmp10
    ->  group by flag
    ->  order by flag ;
+--------------+--------------+
| research     | apps         |
+--------------+--------------+
| 100          | 400          |
|   JONES      |   CORRALES   |
|   TONEY      |   MARQUEZ    |
|   HOPKINS    |   MAYWEATHER |
| 200          |   MOSLEY     |
|   MORALES    |   CASTILLO   |
|   P.WHITAKER | 500          |
|   ROBINSON   |   GATTI      |
|   MARCIANO   |   CALZAGHE   |
| 300          | 600          |
|   LACY       |   HAGLER     |
|   WRIGHT     |   HEARNS     |
|   J.TAYLOR   |   LAMOTTA    |
| NULL         |   FRAZIER    |
| NULL         | 700          |
| NULL         |   JUDAH      |
| NULL         |   MARGARITO  |
| NULL         |   GUINN      |
+--------------+--------------+
18 rows in set (0.00 sec)

这样看起来临时表都10个临时表了，逻辑看起来太复杂了，下面我们拆开来讲解

2.1 分解求出it_apps各部门及员工

我们首先来看看tmp4的结果集:

mysql> with tmp1 as
    -> (
    -> select deptno,
    ->        ename,
    ->        count(*) over (partition by deptno) cnt
    -> from it_apps
    -> order by deptno,ename
    -> ),
    -> tmp2 as
    -> (
    -> select 1 id union select 2
    -> ),
    -> tmp3 as
    -> (
    -> select deptno,
    ->        ename,
    ->        cnt,
    ->        row_number() over w1 as 'rn'
    ->   from tmp1,tmp2
    -> window w1 as (partition by deptno order by id,ename)
    -> ),
    -> tmp4 as
    -> (
    -> select 1 flag1,
    ->        1 flag2,
    ->        case when rn = 1 then deptno else concat('  ',ename) end it_dept
    ->   from tmp3
    ->   where rn <= cnt + 1
    -> )
    -> select * from tmp4;
+-------+-------+--------------+
| flag1 | flag2 | it_dept      |
+-------+-------+--------------+
|     1 |     1 | 400          |
|     1 |     1 |   CORRALES   |
|     1 |     1 |   MARQUEZ    |
|     1 |     1 |   MAYWEATHER |
|     1 |     1 |   MOSLEY     |
|     1 |     1 |   CASTILLO   |
|     1 |     1 | 500          |
|     1 |     1 |   GATTI      |
|     1 |     1 |   CALZAGHE   |
|     1 |     1 | 600          |
|     1 |     1 |   HAGLER     |
|     1 |     1 |   HEARNS     |
|     1 |     1 |   LAMOTTA    |
|     1 |     1 |   FRAZIER    |
|     1 |     1 | 700          |
|     1 |     1 |   JUDAH      |
|     1 |     1 |   MARGARITO  |
|     1 |     1 |   GUINN      |
+-------+-------+--------------+
18 rows in set (0.00 sec)

那么tmp4的结果集是怎么来的呢？
我们来看看tmp1和tmp2以及tmp3
这个地方为什么要有tmp2，一个id为1 和 2 的表，其实因为第一行要显示deptno，后面的才是ename，所以此时应该是n+1，如果只是n，这个地方deptno带不出来。
tmp3就是在tmp1和tmp2的基础上进行了加工，记住order by这个地方一定要正确，不然数据会不准确

tmp3加工完成后，根据每个deptno rn为1的则显示deptno，从2到n+1遍历ename，根据rn <= cnt + 1来进行控制

mysql> select deptno,
    ->        ename,
    ->        count(*) over (partition by deptno) cnt
    -> from it_apps
    -> order by deptno,ename ;
+--------+------------+-----+
| deptno | ename      | cnt |
+--------+------------+-----+
|    400 | CASTILLO   |   5 |
|    400 | CORRALES   |   5 |
|    400 | MARQUEZ    |   5 |
|    400 | MAYWEATHER |   5 |
|    400 | MOSLEY     |   5 |
|    500 | CALZAGHE   |   2 |
|    500 | GATTI      |   2 |
|    600 | FRAZIER    |   4 |
|    600 | HAGLER     |   4 |
|    600 | HEARNS     |   4 |
|    600 | LAMOTTA    |   4 |
|    700 | GUINN      |   3 |
|    700 | JUDAH      |   3 |
|    700 | MARGARITO  |   3 |
+--------+------------+-----+
14 rows in set (0.00 sec)

mysql> select 1 id union select 2 ;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> with tmp1 as
    -> (
    -> select deptno,
    ->        ename,
    ->        count(*) over (partition by deptno) cnt
    -> from it_apps
    -> order by deptno,ename
    -> ),
    -> tmp2 as
    -> (
    -> select 1 id union select 2
    -> )
    -> select deptno,
    ->        ename,
    ->        cnt,
    ->        row_number() over w1 as 'rn'
    ->   from tmp1,tmp2
    -> window w1 as (partition by deptno order by id,ename)
    -> ;
+--------+------------+-----+----+
| deptno | ename      | cnt | rn |
+--------+------------+-----+----+
|    400 | CASTILLO   |   5 |  1 |
|    400 | CORRALES   |   5 |  2 |
|    400 | MARQUEZ    |   5 |  3 |
|    400 | MAYWEATHER |   5 |  4 |
|    400 | MOSLEY     |   5 |  5 |
|    400 | CASTILLO   |   5 |  6 |
|    400 | CORRALES   |   5 |  7 |
|    400 | MARQUEZ    |   5 |  8 |
|    400 | MAYWEATHER |   5 |  9 |
|    400 | MOSLEY     |   5 | 10 |
|    500 | CALZAGHE   |   2 |  1 |
|    500 | GATTI      |   2 |  2 |
|    500 | CALZAGHE   |   2 |  3 |
|    500 | GATTI      |   2 |  4 |
|    600 | FRAZIER    |   4 |  1 |
|    600 | HAGLER     |   4 |  2 |
|    600 | HEARNS     |   4 |  3 |
|    600 | LAMOTTA    |   4 |  4 |
|    600 | FRAZIER    |   4 |  5 |
|    600 | HAGLER     |   4 |  6 |
|    600 | HEARNS     |   4 |  7 |
|    600 | LAMOTTA    |   4 |  8 |
|    700 | GUINN      |   3 |  1 |
|    700 | JUDAH      |   3 |  2 |
|    700 | MARGARITO  |   3 |  3 |
|    700 | GUINN      |   3 |  4 |
|    700 | JUDAH      |   3 |  5 |
|    700 | MARGARITO  |   3 |  6 |
+--------+------------+-----+----+
28 rows in set (0.00 sec)

2.2 it_apps与it_research进行拼接

tmp9得到如下结果:
image.png

此时就可以知道 flag1和flag2的作用了，flag2用来区别是 apps还是research，flag1根据flag2的区别进行累计求和，然后根据新的flag来进行group by，这样就可以将apps和research进行展示了

tmp10返回如下结果:
image.png

有了tmp10的结果，就很可以根据flag直接分组，通过max函数进行求求值。

只是甲
关注
专栏目录
Mysql
weixin_45566322的博客
08-14 249
DML DDL DML(Data Manipulation Language)数据操纵语言： 适用范围：对数据库中的数据进行一些简单操作，如insert,delete,update,select等. DDL(Data Definition Language)数据定义语言： 适用范围：对数据库中的某些对象(例如，database,table)进行管理，如Create,Alter和Drop. 为什么Mysql官方推荐使用自增主键 mysql底层数据结构是 B+ 树，索引对应一棵 B+ 树，mysql 中的数据都
【MySQL】查询顺序与查询SQL
weixin_43091333的博客
03-14 350
【MySQL】查询顺序与查询SQL SQL执行过程 执行过程 我们发送一条SQL给数据库，其实数据库并没有马上执行，而是进行预处理，对SQL进行分析和优化，可以说最终执行的SQL不一定是我们提交的SQL。整个过程非常繁杂，详细过程见下图： 虚拟表 数据库提供临时表、内存表、永久表，虚拟表一般要么是临时表，要么是内存表，用完就删除。永久表则数据会存储在磁盘中。 游标 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。
mysql 两列互换
weixin_30312659的博客
01-02 441
mysql 如果想互换两列的值，直接写 update 表 set col1=col2，col2=col1 这样的后果就是两列都是 col2 的值 注意这和sql server 是不同的， 如果想实现上述功能，添加一个自增列作为标识（必须的）， update 表 a ，表 b set a.col1=c.col2,a.col2=b.col1 where a.id=b.id 也可以...
mysql中的group by使用
u010523770的专栏
10-22 418
话不多说，group by也就是用来分组用的：（只是使用的时候可能会存在下面的情况） 有如下班级表，表中有keyID,班级，班主任三个字段 可以看到通过group by 分组后，1班有2个班主任，2班也有两个班主任，但是他们的keyID分组后，只展示了一个。 我们将keyID为1的班改成11，在做测试，结果如下： 可以看到，1班的分组后，keyID展
企业级元数据管理系统概述
06-04
主要描述了企业级的元数据管理，以及对企业级元数据管理的一个进化发展路线。
Mysql两列值互换
风涛
10-28 2523
如图，表中有两列值，现在要把两列的值做交换，怎么写SQL？ 第一印象：扩展出来一列：cc，然后将bc值给cc。然后ac再更新给bc；然后cc更新到ac；然后删除cc列。 如此一来，太麻烦了。 那么一条SQL怎么改呢？update tb set ac=bc,bc=ac .这样？你会发现两值变成了一样的，失败！！！为啥呢？ 如下，执行ac=bc,第一条ba值更新给了ac，此时ac和bc的值...
【MySQL】数据表行列转换
花北城的博客
08-28 1万+
表行列转置，顾明思议，将表格中一行行的数据，采用SQL脚本转换成列数据。
用SQL给经过两次转置的结果集添加列标题
wzy0623的专栏
10-28 311
用SQL给经过两次转置的结果集添加列标题
mysql 多因子 数据库_数据库交易回测系列二：多因子Alpha策略回测
weixin_35853499的博客
02-02 774
本系列文章将会介绍如何使用DolphinDB优雅而高效的实现量化交易策略回测。本文将介绍在华尔街广泛应用的多因子Alpha策略的回测。多因子模型是量化交易选股中最重要的一类模型，基本思路是找到某些和回报率最相关的指标，并根据这些指标，构建股票投资组合(做多正相关的股票，做空负相关的股票)。多因子模型中，单独一个因子的个股权重一般实现多空均衡(市场中性)，没有暴露市场风险的头寸(beta为0，所以称...
数据库国产化探究及升级改造过程指导
最新发布
企业实战系列集 ●●● https://ximenjianxue.blog.csdn.net
03-18 1950
当前官网最新版本为DM8，相较于目前主流产品架构：不同特性依靠不同内核去实现，DM8独特采用了双存储引擎架构，行存储引擎和列存储引擎可相互配合、协同工作。同时实现了计算层和存储层的分离，同一内核既支持共享存储式集群，也支持分布式事务集群。
MySQL的序列自增
qq_35560000的博客
07-07 2024
MySQL的序列自增 关于MySQL的自增的使用，包括修改自增的序列号与步长 1 基本使用 1.1 生成自增列 create table table_name( # 列名 类别 auto_increment primary key,(默认not null) col_name int auto_increment primary key )engine=innodb default charset=utf8; 1.2 修改目前序号 在使用自增的时候如果有某些数据需要删除，使用的是delete： del
mysql 横竖拼接_Mysql合并结果接横向拼接字段的实现步骤
weixin_27188815的博客
01-19 751
前言近日在做一个报表功能里面有一个这样的需求是统计各部门在某一月入职和离职的人数我的步骤先查出入职的人数SELECT dept ,COUNT(1) rcNumber FROM 员工表WHERE ( 入职时间 != ''OR 入职时间 IS NOT NULL) and DATE_FORMAT(入职时间, '%Y-%m')= '2019-09'GROUP BY 部门IDORDER BY 部门名称查询记...
MySQL交叉表
热门推荐
ACMAIN_CHM的专栏
06-19 2万+
在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx(  id int primary key,  c1 c
mysql获取属性-列头_mysql数据库查表的列名的那个表各个列的属性是什么？
weixin_32175667的博客
01-19 733
COLUMNS表：提供了关于表中的列的信息。详细表述了某个列属于哪个表。各字段说明如下:table_schema 表所有者(对于schema的名称)table_name 表名column_name 列名ordinal_position 列标识号column_default 列的默认值is_nullable 列的为空性。如果列允许 null，那么该列返回 yes。否则，返回 nodata_type ...
mysql 列转置_MySQL 行列转置
weixin_32136817的博客
01-30 372
建立测试表：DROP TABLE IF EXISTS `my_test_2017_03_29`;CREATE TABLE `my_test_2017_03_29` (`id` int(11) NOT NULL,`event_name` varchar(255) DEFAULT NULL,`dt` date DEFAULT NULL,`ev` int(11) DEFAULT NULL,PRIMARY...
MySQL 计算字符在字符串中出现的次数
只是甲的博客
09-17 5471
备注:测试数据库版本为MySQL 8.0 一.需求 计算一个字符或子串在给定的字符串中出现的次数。 例如: 10,CLARK,MANAGER 要计算在这个字符串中有多少个逗号。 二.解决方案 首先计算原始字符串的长度，然后计算去掉逗号后字符串的长度，这两者的差就是逗号在该字符串中出现的次数。 MySQL中使用replace和length函数即可 代码: select (length('10,CLARK,MANAGER') - length(replace('10,CLARK,MANAGER
MySQL检测两个表中是否有相同数据
只是甲的博客
09-02 3949
备注:测试数据库版本为MySQL 8.0 如需要scott用户下建表及录入数据语句，可参考: scott建表及录入数据sql脚本 问题: 要知道两个表或视图中是否有相同的数据(基数和值)。考虑这个视图 create view v as select * from emp where deptno != 10 union all select * from emp where ename = 'WARD'; select * from v; mysql> create view v -&gt
mysql 查询的班级和姓名结果，进行转置
05-25
您可以使用 MySQL 中的 PIVOT 操作将行转换为列来实现结果的转置。以下是一个示例查询语句： ``` SELECT name, IF(class = 'A', score, NULL) AS A, IF(class = 'B', score, NULL) AS B, IF(class = 'C', score...
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值