如何拆分逗号分隔的记录字符串,然后在MySQL中顺序排列? [英] How to split comma separated string of records and arrange then sequentially in MySQL?
本文介绍了如何拆分逗号分隔的记录字符串,然后在MySQL中顺序排列?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想通过MySQL查询从下表中单独选择记录
ID AgentID Name Return Date
1 1,2,3 A 2016-05-22,2016-02-1,2016-1-15
2 2,4 B 2016-03-22,2016-04-1
期望答案
ID AgentID Name Return Date
1 1 A 2016-05-22
1 2 A 2016-02-1
1 3 A 2016-1-15
2 2 B 2016-03-22
2 4 B 2016-04-1
解决方案
您可以使用MySQL SUBSTRING_INDEX().它将在指定的定界符出现之前从给定的逗号分隔字符串中返回子字符串.
尝试一下,看来效果很好:
SELECT ID
,SUBSTRING_INDEX(SUBSTRING_INDEX(t.AgentID, ',', n.n), ',', -1) Agent
,Name
,SUBSTRING_INDEX(SUBSTRING_INDEX(t.Return_Date, ',', n.n), ',', -1) Return_Date
FROM table1 t CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(t.Return_Date) - LENGTH(REPLACE(t.Return_Date, ',', '')))
ORDER BY ID;
检查此. SQL小提琴这里 >
要进一步研究,请继续 MySQL拆分字符串功能
I want to a MySQL query to select the records separately from following table
ID AgentID Name Return Date
1 1,2,3 A 2016-05-22,2016-02-1,2016-1-15
2 2,4 B 2016-03-22,2016-04-1
Expecting Answer
ID AgentID Name Return Date
1 1 A 2016-05-22
1 2 A 2016-02-1
1 3 A 2016-1-15
2 2 B 2016-03-22
2 4 B 2016-04-1
解决方案
You can use MySQL SUBSTRING_INDEX(). It will return the sub-string from the given comma separated string before a specified number of occurrences of the delimiter.
Try this, It seems to work fine:
SELECT ID
,SUBSTRING_INDEX(SUBSTRING_INDEX(t.AgentID, ',', n.n), ',', -1) Agent
,Name
,SUBSTRING_INDEX(SUBSTRING_INDEX(t.Return_Date, ',', n.n), ',', -1) Return_Date
FROM table1 t CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(t.Return_Date) - LENGTH(REPLACE(t.Return_Date, ',', '')))
ORDER BY ID;
Check this.. SQL Fiddle HERE
For further Study go on MySQL Split String Function
这篇关于如何拆分逗号分隔的记录字符串,然后在MySQL中顺序排列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文