MySQL列转行

已于 2023-08-31 10:38:42 修改
阅读量942 收藏 1
点赞数
分类专栏： MySQL 文章标签： 大数据
于 2023-08-30 13:55:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45629153/article/details/132580562
版权

一、列转行

mysql 数据库中，我们可能遇到将数据库中某一列的数据（多个值，按照英文逗号分隔），转化为多行数据（即一行转多行），然后join关联表，再转化为一行数据
如下目标数据，需要按照不同版本维护到表格里面，23-01这样的列名需要转换成对于的行的月份时间和对于的数值

car_codemarketversiondisplay_versioncategorysubcategory23-0123-0223-0323-0423-0523-0623-0723-0823-0823-1023-1123-122023
ELECN2023-065+7manufNA0793911491553892033372981006126310715341
ELECN2023-065+7stockNA0793911491554011913372981006126310715341

转化成这样的结果

car_codemarketversiondisplay_versioncategorysubcategorytime_typestat_timegoal_amountetl_time
ELECN2023-065+7manufNAmth2023-0102023-08-30
ELECN2023-065+7manufNAmth2023-02792023-08-30
ELECN2023-065+7manufNAmth2023-033912023-08-30
ELECN2023-065+7manufNAmth2023-041492023-08-30
ELECN2023-065+7manufNAmth2023-051552023-08-30
ELECN2023-065+7manufNAmth2023-063892023-08-30
ELECN2023-065+7manufNAmth2023-072032023-08-30
ELECN2023-065+7manufNAmth2023-083372023-08-30
ELECN2023-065+7manufNAmth2023-092982023-08-30
ELECN2023-065+7manufNAmth2023-1010062023-08-30
ELECN2023-065+7manufNAmth2023-1112632023-08-30
ELECN2023-065+7manufNAmth2023-1210712023-08-30
ELECN2023-065+7manufNAyear202353412023-08-30
ELECN2023-065+7stockNAmth2023-0102023-08-30
ELECN2023-065+7stockNAmth2023-02792023-08-30
ELECN2023-065+7stockNAmth2023-033912023-08-30
ELECN2023-065+7stockNAmth2023-041492023-08-30
ELECN2023-065+7stockNAmth2023-051552023-08-30
ELECN2023-065+7stockNAmth2023-063892023-08-30
ELECN2023-065+7stockNAmth2023-072032023-08-30
ELECN2023-065+7stockNAmth2023-083372023-08-30
ELECN2023-065+7stockNAmth2023-092982023-08-30
ELECN2023-065+7stockNAmth2023-1010062023-08-30
ELECN2023-065+7stockNAmth2023-1112632023-08-30
ELECN2023-065+7stockNAmth2023-1210712023-08-30
ELECN2023-065+7stockNAyear202353412023-08-30

二、实现方式
把上面行转列的数据放在另外一个视图里面，然后源头数据修改或者增加，视图可以直接看到数据变化。语句如下：

select 
    a.car_code as car_code,
    a.market as market,
    if(b.help_topic_id = 12,'year','mth') as time_type,#具体是月的目标还是年的目标
    case when b.help_topic_id = 0 then '2023-01'
        when b.help_topic_id = 1 then '2023-02'
        when b.help_topic_id = 2 then '2023-03'
        when b.help_topic_id = 3 then '2023-04'
        when b.help_topic_id = 4 then '2023-05'
        when b.help_topic_id = 5 then '2023-06'
        when b.help_topic_id = 6 then '2023-07'
        when b.help_topic_id = 7 then '2023-08'
        when b.help_topic_id = 8 then '2023-09'
        when b.help_topic_id = 9 then '2023-10'
        when b.help_topic_id = 10 then '2023-11'
        when b.help_topic_id = 11 then '2023-12' 
        when b.help_topic_id = 12 then '2023' 
        end as stat_time,#具体时间
    a.category as category,#类别
    a.subcategory as subcategory,#子类别
    a.version as version,#版本,每个月版本可能不一样
    SUBSTRING_INDEX(SUBSTRING_INDEX(a.goal_amount, '#', b.help_topic_id +1),'#',-1) as goal_amount,
    CURRENT_TIMESTAMP as etl_time,#etl时间
    a.display_version as display_version
from
(
    select 
        car_code,
        market,
        version,
        display_version,
        category,
        subcategory,
        concat(`23-01` ,'#',`23-02`,'#',`23-03`,'#',`23-04`,'#',`23-05`,'#',`23-06`,'#',`23-07`,'#',`23-08` ,'#',`23-09`,'#',`23-10`,'#',`23-11`,'#',`23-12`,'#',`2023`) as goal_amount # 先把月目标数据拼接起来变成一个字段
    from  eboard_test.imp_eboard_mgt_plan_all
)a join mysql.help_topic b
 where  b.help_topic_id < ( LENGTH( a.goal_amount) - LENGTH(REPLACE ( a.goal_amount, '#', '' )) + 1 );

mysql.help_topic 的作用是对 SUBSTRING_INDEX 函数出来的数据(也就是按照分割符分割出来的)数据连接起来做笛卡尔积。如果 mysql.help_topic 没有权限，可以自己创建一张临时表，用来与要查询的表连接查询。
三、创建视图

create or replace
algorithm = UNDEFINED view `database_name`.`table_name` (
`car_code`,
`market`,
`time_type`,
`stat_time`,
`category`,
`subcategory`,
`version`,
`goal_amount`,
`etl_time`,
`display_version`
) as
select 
    a.car_code as car_code,
    a.market as market,
    if(b.help_topic_id = 12,'year','mth') as time_type,
    case when b.help_topic_id = 0 then '2023-01'
        when b.help_topic_id = 1 then '2023-02'
        when b.help_topic_id = 2 then '2023-03'
        when b.help_topic_id = 3 then '2023-04'
        when b.help_topic_id = 4 then '2023-05'
        when b.help_topic_id = 5 then '2023-06'
        when b.help_topic_id = 6 then '2023-07'
        when b.help_topic_id = 7 then '2023-08'
        when b.help_topic_id = 8 then '2023-09'
        when b.help_topic_id = 9 then '2023-10'
        when b.help_topic_id = 10 then '2023-11'
        when b.help_topic_id = 11 then '2023-12' 
        when b.help_topic_id = 12 then '2023' 
        end as stat_time,
    a.category as category,
    a.subcategory as subcategory,
    a.version as version,
    SUBSTRING_INDEX(SUBSTRING_INDEX(a.goal_amount, '#', b.help_topic_id +1),'#',-1) as goal_amount,
    CURRENT_TIMESTAMP as etl_time,
    a.display_version as display_version
from
(
    select 
        car_code,
        market,
        version,
        display_version,
        category,
        subcategory,
        concat(`23-01` ,'#',`23-02`,'#',`23-03`,'#',`23-04`,'#',`23-05`,'#',`23-06`,'#',`23-07`,'#',`23-08` 
        ,'#',`23-09`,'#',`23-10`,'#',`23-11`,'#',`23-12`,'#',`2023`) as goal_amount
    from  eboard_test.imp_eboard_mgt_plan_all
)a join mysql.help_topic b
 where  b.help_topic_id < ( LENGTH( a.goal_amount) - LENGTH(REPLACE ( a.goal_amount, '#', '' )) + 1 );```
梦如汐
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql多行转多列，多列转多行
feisanking的博客
09-06 2715
多行转多列，多列转多行
MySQL 把结果集转置为多行
只是甲的博客
11-27 830
备注:测试数据库版本为MySQL 8.0 如需要scott用户下建表及录入数据语句，可参考: scott建表及录入数据sql脚本 一.需求 要把行转换为列，根据原表给定列的每个值创建一个列。 例如，返回每个员工及他们的职位(JOB)，目前的查询返回如下结果集: 希望重新设置为结果集的格式，使每个职位使用一列: 二.解决方案 要解决本例的问题，必须使每个job/ename组合唯一，然后，在使用聚集函数去除null时，不会丢失ename。 使用标量子查询，按empno给每个员工分等级。 使用CASE表达式和聚集
mysql中添加字段
m0_54850467的博客
09-11 4万+
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
搞定mysql的 行转列（7种方法） 和 列转行
最新发布
C18298182575的博客
06-14 135
比较抽象，难以理解。（2）IF(subject=‘语文’,score,0) 作为条件，即对所有subject='语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作，如果score没有值则默认为0。INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);
MySQL中的面试题——行转列（列转行）手把手详解思想
qq_57492774的博客
07-12 9977
在MySQL中，行转列（Pivot）和列转行（Unpivot）是用于改变数据表格布局的概念，行转列和列转行是在特定数据转换需求下使用的技术，可以帮助改变数据的呈现方式，以适应不同的分析和报告要求。行转列：是指将原始数据表格中的行数据转换为列数据。这种操作常用于将某一列的值作为新的列名，并将对应的值填入相应的位置。列转行：列转行是指将原始数据表格中的列数据转换为行数据。这种操作常用于将多个列的值转换为一列，并将原来的列名作为新的列。全面介绍了行转列和列转行的思想，以及引导式地去实现行转列和列转行
MYSQL如何列转行
04-20 1920
我们的动态sql是拼接实现的, 主要就是拼接我们的课程成绩那一句, 所以要先看一下CONCAT函数拼接课程语句。IF(s1,s2,s3)表达式,类似三木运算符取值,s1值为真取s2值,假取s3个值,最后可得到某一科成绩。有三张表，学生表、成绩表和课程表，我们可以通过连表查询出学生姓名、课程及对应的成绩： 所需表sql。然后我们分组且用MAX函数获取每个学生的数学课程的成绩,替换这一课的字段名称。好的，现在呢我们要把课程名称呢变成横行呢？是的，结果就是上面要的MAX函数。好的 那么我们来封装成存储过程。
mysql 列转行的技巧(分享)
09-09
下面小编就为大家带来一篇mysql 列转行的技巧(分享)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
mysql列转行以及年月分组实例
09-09
示例中的查询使用了嵌套结构，外层查询用于实现列转行，内层查询首先完成了原始数据的预处理，包括筛选满足条件的记录、计算不同部门的唯一rect_id数量（zcount）、以及按部门和年月进行初步分组。 ```sql FROM ...
MySql 列转行实例
06-03
总之，MySQL中的列转行操作是一个实用的技巧，尤其在处理多维度数据分析时，能够极大地提升数据的可读性和分析效率。通过存储过程，我们可以根据具体需求灵活地实现这一转换，使得复杂的数据变得更加易管理和解析。
mysql 列转行,合并字段的方法(必看)
12-16
列转行：利用max(case when then) max—聚合函数 取最大值 (case course when ‘语文’ then score else 0 end) —判断 as 语文—别名作为列名 SELECT `name`, MAX( CASE WHEN course='\u8bed\u6587' THEN ...
mysql 列转成多行
chengchong_cc的博客
04-13 383
【代码】mysql 列转成多行。
mysql使用逗号分隔的一行数据转多行数据
pscool的博客
04-15 6309
【代码】mysql使用逗号分隔的一行数据转多行数据。
mysql 指定列转成行
记事本
01-26 768
由于项目需要将列得内容当做字段名 select B.projId,B.compName, B.shortName, B.reguInstitution, B.finaAmount,B.preName, B.brokName, B.market as listBoardCode,(case when B.market=101 then '主板' when B.market=201 then '中小板' when B.market=301 then '创业板' when B.market=302 th
行转列逗号隔开&逗号隔开列转行
jc_benben的专栏
01-27 1万+
ORACLE： SQL> create table t(id int,name varchar2(30)); 表已创建。 SQL> insert into t values(1,'a'); 已创建 1 行。 SQL> insert into t values(1,'b'); 已创建 1 行。 SQL> insert into t values(1,'c...
mysql行转列（拆分字符串场景）
baokx的专栏
07-01 9043
一对多没有建立中间表的时候经常会采用分隔符的形式将“多”存储在“一”的一个字段里，这样做的代价是无法向一对多的时候那样直接关联查询，一般采用在程序中分割后分别查询的办法
MYSQL 逗号分割行转列 方法
程序媛的博客
04-02 1209
https://www.cnblogs.com/jackszc/p/7448322.html
mysql列转行
zcw4237256的博客
05-10 1345
-- 创建表 学生表CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT '学号', `stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名', PRIMARY KEY (`stuid`))COLLATE='utf8_general_ci'ENGINE=InnoDB; -- ...
mysql行转列转换
热门推荐
Coder_PWH
08-23 12万+
mysql 行列转换 ，在项目中应用的极其频繁，尤其是一些金融项目里的报表。其中最为头痛的就是多行转多列，动态的列行转换。最近在研究这些行里转换，还是从最为简单的行列转换开始。sql 脚本 -- 创建表 学生表 CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT '学号', `stunm` VARCHAR(20
mysql 列转行
07-27
你可以使用MySQL的PIVOT操作来进行列转行。Pivot操作允许你将行数据转换为列数据。下面是一个示例： 假设你有一个名为`table_name`的表，其中包含以下数据： ``` +----+-------+------+ | id | name | age | +----...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值