mysql - 将逗号分隔的列表分隔成查询列

Question

我有一个表，其中包含逗号分隔列表的列，如

ID :  List 
1  :  1,2,44,5      --row# 1
2  :  4,3,5,2,56,66 --row# 2

等等。我想编写一个最多包含 10 列 Item1、Item2、Item3 .... Item10 的选择查询，每列都有一个来自相应逗号分隔列表的数字。

例如:对于ID = 1

Item1 = 1, Item2 = 2, Item3 = 44, Item4 = 55 and all other columns would be null or empty

我如何用 SQL 编写它？

Answer 1

你可以这样做:

select
    substring_index(substring_index(str,',',1),',',-1)AS c1
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 1 THEN substring_index(substring_index(str,',',2),',',-1) ELSE NULL END AS c2
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 2 THEN substring_index(substring_index(str,',',3),',',-1) ELSE NULL END AS c3
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 3 THEN substring_index(substring_index(str,',',4),',',-1) ELSE NULL END AS c4
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 4 THEN substring_index(substring_index(str,',',5),',',-1) ELSE NULL END AS c5
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 5 THEN substring_index(substring_index(str,',',6),',',-1) ELSE NULL END AS c6
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 6 THEN substring_index(substring_index(str,',',7),',',-1) ELSE NULL END AS c7
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 7 THEN substring_index(substring_index(str,',',8),',',-1) ELSE NULL END AS c8
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 8 THEN substring_index(substring_index(str,',',9),',',-1) ELSE NULL END AS c9
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 9 THEN substring_index(substring_index(str,',',10),',',-1) ELSE NULL END AS c10
from test

Demo.

表达式有两个共同的部分:

• LENGTH(str)-LENGTH(REPLACE(str,',','')) >= K - 此子表达式确定字符串是否至少有 K分隔符
• substring_index(substring_index(str,',',K),',',-1) - 此子表达式删除第 K 分隔符之后的元素