MySQL行列转换及多选字典

最新推荐文章于 2024-04-09 15:16:08 发布
原创 最新推荐文章于 2024-04-09 15:16:08 发布 · 585 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql

本文介绍如何在MySQL中将包含逗号分隔值的字段进行列转行操作，并结合字典表实现科目名称的匹配，进一步完成成绩数据的整合。通过不同SQL语句的对比，展示如何高效地进行数据转换。

文章目录

数据准备

建表


-- 学生表
-- DROP TABLE tb_stu;
CREATE TABLE tb_stu (
  id int(11) NOT NULL AUTO_INCREMENT,
  userid varchar(20) NOT NULL COMMENT '用户id',
  name varchar(20) DEFAULT NULL COMMENT '姓名',
  subjects varchar(64) DEFAULT NULL COMMENT '科目',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 成绩表
-- DROP TABLE tb_score;
CREATE TABLE tb_score (
  id int(11) NOT NULL AUTO_INCREMENT,
  userid varchar(20) NOT NULL COMMENT '用户id',
  subject varchar(20) DEFAULT NULL COMMENT '科目',
  score double DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 字典表
-- DROP TABLE tb_dic;
CREATE TABLE tb_dic (
  id int(11) NOT NULL AUTO_INCREMENT,
  code varchar(20) NOT NULL COMMENT '字典-代码',
  text varchar(20) DEFAULT NULL COMMENT '字典-文字',
  seq int(2) DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

写入数据

-- 学生表
INSERT INTO tb_stu(userid,name,subjects) VALUES ('001','小明','YW,SX,YY');
INSERT INTO tb_stu(userid,name,subjects) VALUES ('002','小锋','YW,SX,YY');
INSERT INTO tb_stu(userid,name,subjects) VALUES ('002','小军','YW,SX,YY,ZZ');

-- 成绩表
INSERT INTO tb_score(userid,subject,score) VALUES ('001','YW',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','SX',92);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','YY',80);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','YW',88);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','SX',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','YY',75.5);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','YW',70);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','SX',85);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','YY',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','ZZ',82);


-- 字典表
INSERT INTO tb_dic(code,text,seq) VALUES ('YW','语文',1);
INSERT INTO tb_dic(code,text,seq) VALUES ('SX','数学',2);
INSERT INTO tb_dic(code,text,seq) VALUES ('YY','英语',3);
INSERT INTO tb_dic(code,text,seq) VALUES ('ZZ','政治',4);

查询验证

第1步：逗号分割 - 列转行

SQL

SELECT
    stu.name,
    stu.subjects,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stu.subjects, ',', mht.help_topic_id + 1),
            ',',
            - 1) subj
FROM
    test.tb_stu stu
        JOIN
    mysql.help_topic mht ON mht.help_topic_id < (LENGTH(stu.subjects) - LENGTH(REPLACE(stu.subjects, ',', '')) + 1)
ORDER BY stu.userid;

RESULT

useridnamesubjectssubj
001小明YW,SX,YYYW
001小明YW,SX,YYSX
001小明YW,SX,YYYY
002小锋YW,SX,YYYW
002小锋YW,SX,YYSX
002小锋YW,SX,YYYY
003小军YW,SX,YY,ZZYW
003小军YW,SX,YY,ZZSX
003小军YW,SX,YY,ZZYY
003小军YW,SX,YY,ZZZZ

第2步：逗号分割 - 列转行 - 字典匹配

SQL

SELECT
    tst.userid, tst.name, tst.subjects, tst.subj, dic.text
FROM
    (SELECT
        stu.userid,
            stu.name,
            stu.subjects,
            SUBSTRING_INDEX(SUBSTRING_INDEX(stu.subjects, ',', mht.help_topic_id + 1), ',', - 1) subj
    FROM
        tb_stu stu
    JOIN mysql.help_topic mht ON mht.help_topic_id < (LENGTH(stu.subjects) - LENGTH(REPLACE(stu.subjects, ',', '')) + 1)) tst
        LEFT JOIN
    tb_dic dic ON tst.subj = dic.code
ORDER BY tst.userid;

RESULT

useridnamesubjectssubjtext
001小明YW,SX,YYYW语文
001小明YW,SX,YYSX数学
001小明YW,SX,YYYY英语
002小锋YW,SX,YYYW语文
002小锋YW,SX,YYSX数学
002小锋YW,SX,YYYY英语
003小军YW,SX,YY,ZZYW语文
003小军YW,SX,YY,ZZSX数学
003小军YW,SX,YY,ZZYY英语
003小军YW,SX,YY,ZZZZ政治

第3步：逗号分割 - 列转行 - 字典匹配 - 分组合并(行转列)

SQL

  • V1
SELECT
    tst.userid,
    tst.name,
    tst.subjects,
    -- tst.subj,
    -- dic.text,
    GROUP_CONCAT(dic.text) subjects_cn
FROM
    (SELECT
        stu.userid,
            stu.name,
            stu.subjects,
            SUBSTRING_INDEX(SUBSTRING_INDEX(stu.subjects, ',', mht.help_topic_id + 1), ',', - 1) subj
    FROM
        tb_stu stu
    JOIN mysql.help_topic mht ON mht.help_topic_id < (LENGTH(stu.subjects) - LENGTH(REPLACE(stu.subjects, ',', '')) + 1)) tst
        LEFT JOIN
    tb_dic dic ON tst.subj = dic.code
GROUP BY tst.userid , tst.name , tst.subjects
ORDER BY tst.userid;
  • V2
SELECT
    stu.userid,
    stu.name,
    stu.subjects,
    (SELECT
            GROUP_CONCAT(dic.text) subjects_cn
        FROM
            tb_dic dic
        WHERE
            1 = 1
                AND dic.code IN (SELECT DISTINCT
                    (REGEXP_SUBSTR(T.subjects, '[^,]+', 1, mht.help_topic_id))
                FROM
                    (SELECT stu.subjects) T
                        JOIN
                    mysql.help_topic mht ON mht.help_topic_id <= (CHAR_LENGTH(T.subjects) - CHAR_LENGTH(REPLACE(T.subjects, ',', '')) + 1))) subjects_cn
FROM
    tb_stu stu
ORDER BY stu.userid
  • V3 [2021/11/9 13:56]
SELECT 
    stu.userid,
    stu.name,
    stu.subjects,
    (SELECT 
            GROUP_CONCAT(dic.text)
        FROM
            tb_dic dic
        WHERE
            FIND_IN_SET(dic.code, stu.subjects)) subjects_cn
FROM
    tb_stu stu
ORDER BY stu.userid

RESULT

useridnamesubjectssubjects_cn
001小明YW,SX,YY语文,数学,英语
002小锋YW,SX,YY语文,数学,英语
003小军YW,SX,YY,ZZ语文,数学,英语,政治

第4步：逗号分割 - 列转行 - 字典匹配 - 分组合并(行转列) - 其他表

SQL

  • V1
SELECT
    tst.userid,
    tst.name,
    tst.subjects,
    -- tst.subj,
    -- dic.text,
    GROUP_CONCAT(dic.text) subjects_cn,
    GROUP_CONCAT(dic.text, ':', sco.score) scores_cn
FROM
    (SELECT
        stu.userid,
            stu.name,
            stu.subjects,
            SUBSTRING_INDEX(SUBSTRING_INDEX(stu.subjects, ',', mht.help_topic_id + 1), ',', - 1) subj
    FROM
        tb_stu stu
    JOIN mysql.help_topic mht ON mht.help_topic_id < (LENGTH(stu.subjects) - LENGTH(REPLACE(stu.subjects, ',', '')) + 1)) tst
        LEFT JOIN
    tb_dic dic ON tst.subj = dic.code
        LEFT JOIN
    tb_score sco ON tst.userid = sco.userid
        AND tst.subj = sco.subject
GROUP BY tst.userid , tst.name , tst.subjects
ORDER BY tst.userid;
  • V2 [2021/11/9 14:03]
SELECT 
    stu.userid,
    stu.name,
    stu.subjects,
    (SELECT 
            GROUP_CONCAT(dic.text, ':', sco.score)
        FROM
            tb_dic dic
        WHERE
            FIND_IN_SET(dic.code, stu.subjects)) scores_cn
FROM
    tb_stu stu
        LEFT JOIN
    tb_score sco ON stu.userid = sco.userid
ORDER BY stu.userid

RESULT

useridnamesubjectssubjects_cnscores_cn
001小明YW,SX,YY语文,数学,英语语文:90,数学:92,英语:80
002小锋YW,SX,YY语文,数学,英语语文:88,数学:90,英语:75.5
003小军YW,SX,YY,ZZ语文,数学,英语,政治语文:70,数学:85,英语:90,政治:82

其他方式

SUBSTRING_INDEX

  • MySQL8下
with numbers as (
  select 1 as n
  union select 2 as n
  union select 3 as n
  union select 4 as n
  union select 5 as n
  union select 6 as n
  union select 7 as n
  union select 8 as n
  union select 9 as n
)
SELECT
    stu.userid,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stu.subjects, ',', n),
            ',',
            - 1) AS sub_value
FROM
    tb_stu stu
        JOIN
    numbers ON CHAR_LENGTH(stu.subjects) - CHAR_LENGTH(REPLACE(stu.subjects, ',', '')) >= n - 1
    order by stu.userid

REGEXP_SUBSTR

SELECT
    stu.userid,
    REGEXP_SUBSTR(stu.subjects, '[^,]+', 1, mht.help_topic_id) AS sub_value
FROM
    tb_stu stu
        JOIN
    mysql.help_topic mht ON mht.help_topic_id <= (CHAR_LENGTH(stu.subjects) - CHAR_LENGTH(REPLACE(stu.subjects, ',', '')) + 1)
ORDER BY stu.userid

参考

Links

rox
关注
【SQL应知应会】行列转换（一）• MySQL版
爱书不爱输的程序猿
06-27 2055
1.准备操作2.行转列1.1为何进行行转列？1.2 行转列有两个意思：1.表内的行转列 2.跨表的行转列3.行转列的思路：行变少，列变多3.1 如何进行行转列：增加字段，进行聚合(行变少)4.行转列的实操4.1 通用的行转列(Mysql和Oracle都能用)4.1.1想在结果中加入学生名字4.1.1.1加入名字的方法1：
【SQL应知应会】行列转换（二）• MySQL版
爱书不爱输的程序猿
07-01 1万+
在前一篇内容中，学习了MySQL的行列转换中的行转列，其中只讲述了在MySQL与Oracle中通欧诺个的行转列，并且进行了对应的扩展了——如果想在结果中加入学生姓名的方法，上一篇讲了其中一种方法，就是使用关联子查询。 今天这篇内容，将继续进行讲述MySQL的行列转换的后续内容，其中包括添加学生姓名的第二种方法(使用了join进行关联表)，以及本文章主攻的核心内容——行列转换中的列转行。 同样的，为了大家可以更方便的一起跟着文章进行代码的操作学习，在文章中的每一块的知识点都提供了对应的数据准备，即大家可以直
mysql字典类别转换_MySQL行列转换及多选字典
weixin_35348630的博客
02-02 424
[TOC] [^_^]: MySQL行列转换及多选字典 2021/1/18 10:50数据准备建表-- 学生表-- DROP TABLE tb_stu;CREATE TABLE tb_stu (id int(11) NOT NULL AUTO_INCREMENT,userid varchar(20) NOT NULL COMMENT '用户id',name varchar(20) DEFAULT ...
mysql字典类别转换_mysql-笔记-类型转化
weixin_33439618的博客
02-02 362
1 concat() 隐式转化为字符串2 cast( 1 as char) 显示转化3 比较中的隐式转化null 与任何值比例都是null ； 除了使用 null-safe <=> 相等比较 select null=1; null select null <=>1 false ; select null <=>null true如果比较的2个参数都是字符串...
为了成功上岸之字符串代码总结
weixin_44006670的博客
09-14 908
为了成功上岸之字符串代码总结整数转字符串（不用itoa） 整数转字符串（不用itoa） char* itoa(int value,char*string,int radix); value: 要转换的整数，string: 转换后的字符串,radix: 转换进制数，如2,8,10,16 0（数字零）的ASCII码是48；A的ASCII码是65；a的ASCII码是97； 负号要考虑 在这里插入代码片 ...
mysql字典类别转换_MySQL 数据类型
weixin_42131316的博客
12-23 764
MySQL 数据类型MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。数值类型MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLEPRECISION)。关键字INT是...
Jquery多选数据字典插件
cpsl26415的博客
04-10 250
Jquery多选数据字典插件[@more@]/** *多选的数据字典 *Date: 2012-04-10 *孙伟 */(function($){ $.fn.dropdownlist=function(opts){ var se...
mysql行列转换
03-02
java语言 数据库 mysql数据库 sql行列转换的例子 详解
Mysql实现行列转换
热门推荐
m0_67266585的博客
09-11 1万+
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
巧用mysql位运算解决多选值存储问题
朱守成
11-17 2796
一．问题场景 工作中经常遇到多选值存储问题，例如：用户有多种认证方式，密码认证、短信认证、扫码认证等，一个用户可能只开启了其中某几种认证方式。 二. 场景分析 比较容易理解的两种实现方式，多字段存储、单个字段拼接存储。 1.多字段存储 每种认证方式用一个字段存储，0表示未开启，1表示已开启。 缺点：每增加一种认证方式都需要添加一个表字段，扩展性差。 2.单字段拼接 单字段存储，已开启的认证方式用逗号(或其他分割符)拼接。例如：开始了密码认证和短信认证，则存储为：密码认证,短信认证。 缺点：不利
Mysql 逗号分割的字典值转换相对应的字典名称
weixin_39093006的博客
01-18 1957
SELECT sct.city_code, GROUP_CONCAT(sdi.`name`) FROM sys_contract sct LEFT JOIN sys_dict_item sdi ON FIND_IN_SET(sdi.`code`, sct.city_code) > 0 WHERE sdi.data_dict_code = 10002 GROUP BY sct.id
sql更新，字典值转换，
沉淀的博客
06-18 2168
update population set education_degree = (CASE when education_degree='博士' THEN 0 when education_degree='研究生' THEN 1 when education_degree='本科' THEN 2 when education_degree='大专' THEN 3 when educatio...
随笔之pymysql查询结果转字典dict
家住海边，见过大风大浪
03-20 5926
直接上可用代码 import mysql def get_sql_conn(): """ 获取数据库连接 """ conn= pymysql.connect(host="*.*.*.*",user="username",password="password",db="dbname") cursor = conn.cursor() return co...
MySQL如何将一个字段中串连起来的字典码值全部转换成对应中文
最新发布
xiaofanxu08的博客
04-09 754
最近做的一个项目中，有一个码值转换的需求，需要把一个字段中串连起来的字典码值全部转换成对应的中文，网上查了下发现MySQL好像没有现成的函数能拿来用，于是只好自己动手。比如下面这张sys_dept表，ancestors表示该组织的所有上级组织，需要把这些上级组织全部用中文+‘-’的形式串联起来。两种思路虽然使用的方式不太一样，但原理是相同的。
db2 多值数据字典解析
huryer的专栏
03-19 447
问题描述： 一般情况下，业务数据中存储的字典值为单值，比如"1"或"2"，直接关联字典表后，可以解析出字典值； 但有时因业务要求，需要存储多选值，比如：“1,2,11,12”，此时，无法直接关联字典表进行解析； 本文讲解如下通过函数,进行多值字典解析； 适用范围 DB2 9.7/10.5 示例数据 业务字典表： GUPID GUPNAME 1 分组1 2 分组2 3 分组3...
mysql行列转换问题_Mysql行列转换
weixin_42102272的博客
01-19 272
Table pivot行翻转2019年6月6日9:16标准化的数据库是非常强大的，在数据库的开发中，开发者需要竭尽所能的完善的数据保存的方法，同时保证读写的速度，最终一般通过三泛式来解决大多数问题，有时候我们需要信息以行的方法表现但是其分布在多个列中，这是我们就需要使用行翻转来解决问题了；假设数据库存在以下三个表UsersItemsUser_ItemsUser_Items是用户以物品之间的一个多对...
mysql将字段导出为数据字典
摘星辰Li
05-16 860
导出代码如下： SELECT COLUMN_NAME 字段名, COLUMN_TYPE 数据类型, IS_NULLABLE AS '允许为空', COLUMN_COMMENT 字段说明 FROM INFORMATION_SCHEMA.COLUMNS where -- student_management为数据库名称，到时候只需要修改成你要导出表结构的数据库即可 table_schema ='stu...
Oracle数据库用SQL语句转换数据字典（一个字段多个值）
你是否得到了你想要的人生
05-14 5140
Oracle数据用SQL语句转换数据字典（一个字段多个值） 最近遇到一个让人非常头疼的问题，在开发过程中一个视图需要将字段的Code转换成数据字典中的Name,例：需要转换的字段格式：（code,code1,code2）转换成格式为：（name,name1,name2)字段以逗号分隔多个值，分享一下脱坑的过程。 1、首先想到得是这种数据用循环处理是最方便的，但是视图中不能用循环非常操蛋，然后想着写一个函数（FUNCTION）来循环处理这种数据,但是查了一下需要做的工作量很大，而且需要需要判断指定的cod
python3 pymysql 查询结果转字典dict
唐国强的博客
01-31 1779
【代码】python3 pymysql 查询结果转字典dict。
掌握MySQL行列转换技巧与示例表结构解析
在处理MySQL数据库时，经常会遇到需要将数据从行格式转换为列格式（行转列）或从列格式转换为行格式（列转行）。MySQL中的这两种操作通常可以通过SQL语句中的聚合函数和条件语句实现，尽管在某些复杂情况下可能需要...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值