mysql 列转行的技巧(分享)

前言：

由于很多业务表因为历史原因或者性能原因，都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值（具体结构见下表）。

这种模式下，应用常常需要将这个列依据分隔符进行分割，并得到列转行的结果。

表数据：

ID Value
1 tiny,small,big
2 small,medium
3 tiny,big

期望得到结果：

ID Value
1 tiny
1 small
1 big
2 small
2 medium
3 tiny
3 big

正文：

#需要处理的表
create table tbl_name (ID int ,mSize varchar(100));
insert into tbl_name values (1,'tiny,small,big');
insert into tbl_name values (2,'small,medium');
insert into tbl_name values (3,'tiny,big');

#用于循环的自增表
create table incre_table (AutoIncreID int);
insert into incre_table values (1);
insert into incre_table values (2);
insert into incre_table values (3);
select a.ID,substring_index(substring_index(a.mSize,',',b.AutoIncreID),',',-1)
from
tbl_name a
join
incre_table b
on b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,',',''))+1)
order by a.ID;

原理分析：

这个join最基本原理是笛卡尔积。通过这个方式来实现循环。

以下是具体问题分析：

length(a.Size) - length(replace(a.mSize,',',''))+1  表示了，按照逗号分割后，改列拥有的数值数量，下面简称n

join过程的伪代码：

根据ID进行循环

{

判断：i 是否 <= n

{

获取最靠近第 i 个逗号之前的数据， 即 substring_index(substring_index(a.mSize,',',b.ID),',',-1)

i = i +1

}

ID = ID +1

}

总结：

这种方法的缺点在于，我们需要一个拥有连续数列的独立表（这里是incre_table)。并且连续数列的最大值一定要大于符合分割的值的个数。

例如有一行的mSize 有100个逗号分割的值，那么我们的incre_table 就需要有至少100个连续行。

当然，mysql内部也有现成的连续数列表可用。如mysql.help_topic： help_topic_id 共有504个数值，一般能满足于大部分需求了。

改写后如下：

select a.ID,substring_index(substring_index(a.mSize,',',b.help_topic_id+1),',',-1)
from
tbl_name a
join
mysql.help_topic b
on b.help_topic_id < (length(a.mSize) - length(replace(a.mSize,',',''))+1)
order by a.ID;

以上这篇mysql 列转行的技巧(分享)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持我们。

赞 (0)

相关推荐

  • mysql列转行以及年月分组实例

    如下所示: SELECT count(DISTINCT(a.rect_id)) zcount, a.job_dept,  DATE_FORMAT(submit_date, '%Y-%m') zsubmit_date  FROM  表名 a  WHERE  a.statu = 3  AND a.rstatu = 2  AND a.job_dept IN ('19', '20', '21')  GROUP BY  a.job_dept,  DATE_FORMAT(submit_date, '%Y-%

  • mysql 列转行,合并字段的方法(必看)

    mysql 列转行,合并字段的方法(必看)

    数据表: 列转行:利用max(case when then) max---聚合函数 取最大值 (case course when '语文' then score else 0 end) ---判断 as 语文---别名作为列名 SELECT `name`, MAX( CASE WHEN course='语文' THEN score END ) AS 语文, MAX( CASE WHEN course='数学' THEN score END ) AS 数学, MAX( CASE WHEN cour

  • mysql 行转列和列转行实例详解

    mysql 行转列和列转行实例详解

    mysql行转列.列转行 语句不难,不做多余解释了,看语句时,从内往外一句一句剖析 行转列 有如图所示的表,现在希望查询的结果将行转成列 建表语句如下: CREATE TABLE `TEST_TB_GRADE` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `USER_NAME` varchar(20) DEFAULT NULL, `COURSE` varchar(20) DEFAULT NULL, `SCORE` float DEFAULT '0', PRIM

  • mysql 列转行的技巧(分享)

    前言: 由于很多业务表因为历史原因或者性能原因,都使用了违反第一范式的设计模式.即同一个列中存储了多个属性值(具体结构见下表). 这种模式下,应用常常需要将这个列依据分隔符进行分割,并得到列转行的结果. 表数据: ID Value 1 tiny,small,big 2 small,medium 3 tiny,big 期望得到结果: ID Value 1 tiny 1 small 1 big 2 small 2 medium 3 tiny 3 big 正文: #需要处理的表 create tabl

  • pandas 使用均值填充缺失值列的小技巧分享

    pd.DataFrame中通常含有许多特征,有时候需要对每个含有缺失值的列,都用均值进行填充,代码实现可以这样: for column in list(df.columns[df.isnull().sum() > 0]): mean_val = df[column].mean() df[column].fillna(mean_val, inplace=True) # -------代码分解------- # 判断哪些列有缺失值,得到series对象 df.isnull().sum() > 0

  • 一文弄懂MYSQL如何列转行

    一文弄懂MYSQL如何列转行

    目录 一.需求: 二.如何实现 1)首先看我们的静态SQL 2)那么就有人问了,如果我有100门课程不是要写100次名称,这也太麻烦了? 3)这样每次都写一长串sql也很麻烦? 总结 一.需求: 有三张表,学生表.成绩表和课程表,我们可以通过连表查询出学生姓名.课程及对应的成绩: 所需表sql -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TA

  • MySQL中列转行和行转列总结解决思路

    目录 引言 列转行 行转列 总结 引言 在学习sql中遇到了列转行和行转列的题目,这里总结一下如何在对应的情景下解决不同的题目: 列转行 创建一个表stu_score_01: SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for stu_score_01 -- ---------------------------- DROP TABLE IF E

  • MySQL性能优化技巧分享

    MySQL性能优化 在互联网公司MySQL的使用非常广泛,大家经常会有MySQL性能优化方面的需求.整理了一些在MySQL优化方面的实用技巧. Schema与数据类型优化 整数通常是标识列最好的选择,因为它们很快并且可以使用AUTO_INCREMENT 完全"随机"的字符串(如:MD5().SHA1()或者UUID()等产生的字符串)会任意分布在很大的空间内,会导致INSERT以及一些SELECT语句变的很慢 如果希望查询执行得快速且并发性好,单个查询最好不要做太多的关联查询(互联网公

  • SQL行转列和列转行代码详解

    行列互转,是一个经常遇到的需求.实现的方法,有case when方式和2005之后的内置pivot和unpivot方法来实现. 在读了技术内幕那一节后,虽说这些解决方案早就用过了,却没有系统性的认识和总结过.为了加深认识,再总结一次. 行列互转,可以分为静态互转,即事先就知道要处理多少行(列);动态互转,事先不知道处理多少行(列). --创建测试环境 USE tempdb; GO IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orde

  • Oracle Decode()函数使用技巧分享

    今天上头要求做一个类似的功能,初步想到的列转行,但是如何实现也没有什么好办法,这个函数不错 DECODE函数是ORACLE PL/SQL是功能强大的函数之一,目前还只有ORACLE公司的SQL提供了此函数,其他数据库厂商的SQL实现还没有此功能.DECODE有什么用途 呢? 先构造一个例子,假设我们想给智星职员加工资,其标准是:工资在8000元以下的将加20%:工资在8000元以上的加15%,通常的做法是,先选出记录 中的工资字段值? select salary into var-salary

随机推荐