MySql 一行变多行(根据特定符号分割)_mysql 将日期按行展开_ChaseDreamBoy的博客

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
相关文章推荐
想旅行的热水瓶 · 修改 IIS ...· 2 年前 ·
精明的皮带 · pyqt5 简单例子 --- ...· 2 年前 ·
骑白马的菠萝 · Python创建二维数组(关于list的一个 ...· 2 年前 ·
DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT ,
`name` varchar(255) DEFAULT NULL,
`num` int(8),
PRIMARY KEY (`id`)
INSERT INTO `test`(`name`, `num`) VALUES ('a1,b258,c', 11);
INSERT INTO `test`(`name`, `num`) VALUES ('f,g123456,h,i85,j', 33);
INSERT INTO `test`(`name`, `num`) VALUES ('d,e1234', 22);
那么: SELECT * FROM test; : 
那么，把数据转换成为下面这样，需要怎么样实现呢: 
二、普通 sql 实现(需要依赖 mysql.help_topic 表)
 
SELECT
    a.id,a.num,SUBSTRING_INDEX( SUBSTRING_INDEX( a.`name`, ',', b.help_topic_id + 1 ), ',',-1 ) name
    test a
    JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.`name`) - LENGTH( REPLACE ( a.`name`, ',', '' ) ) + 1 );
三、mysql.help_topic 无权限处理办法
 
mysql.help_topic 的作用是对 SUBSTRING_INDEX 函数出来的数据(也就是按照分割符分割出来的)数据连接起来做笛卡尔积。 
如果 mysql.help_topic 没有权限，可以自己创建一张临时表，用来与要查询的表连接查询。 
获取该字段最多可以分割成为几个字符串: 
SELECT MAX(LENGTH(a.`name`) - LENGTH(REPLACE(a.`name`, ',', '' )) + 1) FROM `test` a;
创建临时表，并给临时表添加数据: 
临时表必须有一列从 0 或者 1 开始的自增数据
临时表表名随意，字段可以只有一个
临时表示的数据量必须比 MAX(LENGTH(a.name) - LENGTH(REPLACE(a.name, ',', '' )) + 1) 的值大 
DROP TABLE IF EXISTS `tmp_help_topic`;
CREATE TABLE IF NOT EXISTS `tmp_help_topic` (
  `help_topic_id` bigint(20) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`help_topic_id`)
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
SELECT
    a.id,a.num,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`name`, ',', b.help_topic_id), ',',-1 ) name
    test a
    JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`name`) - LENGTH(REPLACE(a.`name`, ',', '')) + 1 );
四、函数的意思
 
4.1 REPLACE 函数:
 
把 字符串   a,b,c,d   里面的逗号替换成空字符串
SELECT REPLACE('a,b,c,d', ',', '');
-- 输出: abcd
# 获取逗号的个数
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')));
# 按照逗号分割后会有几个元素，这里分割后就是 a b c d，就是 4 个元素 
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')) + 1);
4.2 SUBSTRING_INDEX 函数:
 
SUBSTRING_INDEX 是字符串截取函数 
SUBSTRING_INDEX(str, delim, count)
str : 表示需要拆分的字符串
delim : 表示分隔符，通过某字符进行拆分
count : 当 count 为正数，取第 n 个分隔符之前的所有字符；当 count 为负数，取倒数第 n 个分隔符之后的所有字符。 
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 1); -- 返回: a
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 2); -- 返回: a*b 
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 3); -- 返回: a*b*c
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 4); -- 返回: a*b*c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -1); -- 返回: d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -2); -- 返回: c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -3); -- 返回: b*c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -4); -- 返回: a*b*c*d
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 1), '*', -1); -- 返回: a
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 2), '*', -1); -- 返回: b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 3), '*', -1); -- 返回: c
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 4), '*', -1); -- 返回: d
五、一行变多行原理
 
回到 sql: 
SELECT
    a.id,a.num,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`name`, ',', b.help_topic_id), ',',-1 ) 
    test a
    JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`name`) - LENGTH(REPLACE(a.`name`, ',', '')) + 1 );
SUBSTRING_INDEX(SUBSTRING_INDEX(a.name, ',', b.help_topic_id), ',',-1 ) 就是获取 tmp_help_topic 表的 help_topic_id 字段的值作为 name 字段的第几个子串
使用了 join 就会把字段 name 分为 (LENGTH( a.name) - LENGTH(REPLACE(a.name, ',', '')) + 1 ) 行，并且每行的字段刚好是 name 字段的第 help_topic_id 个子串
                                    这两天解决了一个比较麻烦的问题，在此记录一下。
问题，select * from table where table.key in (’${权限值}’)  这个sql传入的参数是一个字符串根据情况需要拆分。
因为mysql无法直接一行拆多行，所以百度一下，下面是可行的解决方案。
SELECT
    substring_index(substring_index( a.rn,',',b.help_topic_id + 1    ),',' ,- 1) AS rn
    (select '1,2,
                                    按照指定字符进行合并或拆分是经常碰到的场景，MySQL在合并的写法上比较简单，但是按指定字符拆分相对比较麻烦一点(也就是要多写一些字符)。本文将举例演示如何进行按照指定字符合并及拆分。01 合并MySQL数据库中按照指定字符合并可以直接用group_concat来实现。创建测试表mysql> create table  tb_group(id int auto_increme...
将该条数据按照TXYMZL拆分为两条数据，我上网也查了一下，看到普遍是有两种做法：
第一种：写存储过程来解决这个问题，但是用mysql一般不推荐使用存储过程，因为以后数据库版本升级或者数据迁移比较困难。
第二种：使用mysql自带的一张叫做help_topic的自增序列表，可以解决这个问题。（用该方法的时候，...
                                    注：要是有哪个函数看不懂依次的查，慢慢就看懂了，不要先进行复杂的操纵，自己建个简单的测试一下，成功之后你自己就有自己的感觉了，要不然直接复制粘贴一脸懵逼。
上面的图片是目标拆分的代码，根据‘，’来进行拆分得到多行数据。效果如下
话不多说，上代码：
SELECT
  t.targetColumn,
  substring_index(
    substring_index(t.sourceColumn,'/',b.help_topic_id + 1),'/' ,- 1
  syjxdat
	EXTRACT(YEAR FROM create_time) AS YEAR,
	EXTRACT(MONTH FROM create_time) AS MONTH,
	EXTRACT(DAY FROM create_time) AS DAY
	train_comment
ORDER BY
	create_time DESC;
                                    统计当天sql语句为：*  user where date(log_time) = curdate();curdate()默示当天日期统计前一天若是默示前一天的数据，则不克不及应用curdate()-1，因为当日期为月初时，curdate()-1 日期就不是上一个月的月末日期。例如：今天是6月1日，理论上curdate()-1为5月31日，然则curdate()-1获得不是5月31日，而是6月0日...
select substring_index(substring_index('a,b,c,d',',',help_topic_id+1),',',-1) as test_val
from mysql.help_topic
where help_topic_id<(length('a,b,c,d')-length(replace('a,b,c,d',',',''))+1);
select 
dv.order_id
,dv.deliver_time
,substring_i