如何将字符串中的定界列表解析为行? [英] How can I parse delimited list in string to rows?
本文介绍了如何将字符串中的定界列表解析为行?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我以前的问题:如何在MySQL中合并两个由逗号分隔的数字字符串?
出于以下原因，我试图使用定界列表:
I tried to use delimited list with these reasons:
- 数据超过2000行.
- 我只有一天.
- 这不是生产水平
- 我需要用手分析数据.
我以为我别无选择.但是我发现了这一点: SQL将值拆分为多行
I thought I don't have any choice. But I found this: SQL split values to multiple rows
因此，我从中获得了一些希望.但是将它应用于我的表是非常困难的.
So, I got some hopes from it. But it's quite difficult to apply it to my table.
- Mine is different form. I have multiple columns and I'd like to avoid duplicate row.
- I rather UPDATE and INSERT than SELECT because changing the formation of table will be easier to manage with this reason:Is storing a delimited list in a database column really that bad?
- I need to find the non listed numbers. For example, if the time value is 1, 3, 8 then I need 2,4,5,6,7,9,10,11,12,13 (between 1 and 13)
我的示例表如下:
+-----+------+--------+------+-------+-----------+-------------+
| cid | sid | type | day | time | building | room_number |
+-----+------+--------+------+-------+-----------+-------------+
| 1 | 1 | daytime | mon | 6,7,8 | sky | 507 |
| 2 | 2 | daytime | thu | 2,3,4 | nuri | 906 |
| 3 | 3 | daytime | tue | 6,7,8 | nuri | 906 |
| 4 | 4 | daytime | thu | 6,7 | sky | 1003 |
| 5 | 5 | daytime | mon | 2,3,4 | sky | 507 |
| 6 | 6 | daytime | wed | 6,7,8 | belief | 1003 |
| 7 | 7 | daytime | mon | 2,3,4 | belief | 905 |
| 8 | 8 | daytime | fri | 6,7,8 | truth | 905 |
| 9 | 9 | daytime | tue | 6,7,8 | truth | 905 |
| 10 | 10 | daytime | fri | 2,3,4 | truth | 905 |
| 11 | 11 | daytime | wed | 6,7,8 | truth | 905 |
| 12 | 12 | daytime | fri | 2,3,4 | truth | 1003 |
| 13 | 13 | daytime | mon | 6,7,8 | truth | 905 |
| 14 | 14 | daytime | tue | 2,3,4 | truth | 905 |
| 15 | 15 | daytime | tue | 6,7,8 | sky | 208 |
| 16 | 16 | daytime | tue | 2,3,4 | sky | 208 |
| 17 | 17 | daytime | tue | 2,3,4 | truth | 1004 |
| 18 | 19 | daytime | mon | 2,3,4 | sky | 208 |
| 19 | 20 | daytime | thu | 2,3,4 | truth | 1003 |
| 20 | 21 | daytime | wed | 6,7,8 | sky | 208 |
| 21 | 22 | night | tue | 4,5,6 | nuri | 405 |
| 22 | 23 | night | tue | 1,2,3 | nuri | 405 |
| 23 | 24 | night | tue | 1,2,3 | nuri | 306 |
| 24 | 25 | night | thu | 1,2,3 | nuri | 205 |
| 25 | 26 | night | thu | 4,5,6 | sky | 306 |
| 26 | 27 | night | wed | 1,2,3 | nuri | 306 |
| 27 | 28 | night | wed | 4,5,6 | sky | 309 |
| 28 | 29 | night | wed | 4,5,6 | nuri | 407 |
| 29 | 30 | night | tue | 4,5,6 | nuri | 306 |
| 30 | 31 | night | thu | 1,2,3 | nuri | 307 |
| 31 | 0 | always | | | sky | 201 |
| 32 | 0 | always | | | sky | 202 |
| 33 | 0 | always | | | sky | 203 |
| 34 | 0 | always | | | sky | 204 |
| 35 | 0 | always | | | nuri | 205 |
| 36 | 0 | always | | | nuri | 206 |
| 37 | 0 | always | | | truth | 207 |
| ... | ... | ... | | | ... | ... |
| 2000 | 0 | always | | | belief | 1101 |
+-----+------+--------+------+-------+-----------+-------------+
- 507 |星期一的空中建筑:已安排2,3,4,6,7,8.因此，将添加1,5,9,10,11,12,13.
- 906 | nuri大楼在星期二，星期四有时间表.这是不同的一天.因此，它们不会互相影响.因此，在星期四&上的1,5,6,7,8,9,10,11,12,13星期二1,2,3,4,5,9,10,11,12,13.
- 如果有重复的行，则不会影响.
我对表更改的期望是:
+-----+------+--------+------+-------+-----------+-------------+
| cid | sid | type | day | time | building | room_number |
+-----+------+--------+------+-------+-----------+-------------+
| 1 | 1 | daytime | mon | 1 | sky | 507 |
| 2 | 1 | daytime | mon | 5 | sky | 507 |
| 3 | 1 | daytime | mon | 9 | sky | 507 |
| 4 | 1 | daytime | mon | 10 | sky | 507 |
| 5 | 1 | daytime | mon | 11 | sky | 507 |
| 6 | 1 | daytime | mon | 12 | sky | 507 |
| 7 | 1 | daytime | mon | 13 | sky | 507 |
| 8 | 2 | daytime | thu | 1 | nuri | 906 |
| 9 | 2 | daytime | thu | 5 | nuri | 906 |
| 10 | 2 | daytime | thu | 6 | nuri | 906 |
| 11 | 2 | daytime | thu | 7 | nuri | 906 |
| 12 | 2 | daytime | thu | 8 | nuri | 906 |
| 13 | 2 | daytime | thu | 9 | nuri | 906 |
| 14 | 2 | daytime | thu | 10 | nuri | 906 |
| 15 | 2 | daytime | thu | 11 | nuri | 906 |
| 16 | 2 | daytime | thu | 12 | nuri | 906 |
| 17 | 2 | daytime | thu | 13 | nuri | 906 |
| 18 | 3 | daytime | tue | 1 | nuri | 906 |
| 19 | 3 | daytime | tue | 2 | nuri | 906 |
| 20 | 3 | daytime | tue | 3 | nuri | 906 |
| ... | ... | ... | | | ... | ... |
| 302 | 0 | always | | | nuri | 206 |
| 303 | 0 | always | | | truth | 207 |
| ... | ... | ... | | | ... | ... |
| 4020 | 0 | always | | | belief | 1101 |
+-----+------+--------+------+-------+-----------+-------------+
我这样做的原因是源数据是上课的时间表.而且我想找到空余时间，以便人们在上课的任何时候都可以使用教室.
The reason why I do this is the origin data is the time schedule of classes. And I want to find empty time so that people can use the class room during the time there's any classes.
推荐答案
这里是一种使用追溯查询将字符串拆分为行的方法.然后，您可以使用此结果集来识别丢失的记录:为此，您可以使用时间列表cross join原始表，并使用反left join提取丢失的记录:
Here is an approach using a recusive query to split the strings into rows. You can then use this resultset to identify the missing records: for this, you can cross join the original table with the list of times, and use an anti-left join to pull out missing records:
with recursive
cte as (
select cid, sid, type, day, room_number,
substring(time, 1, locate(',', time) - 1) time,
substring(concat(time, ','), locate(',', time) + 1) rest
from mytable
union all
select cid, sid, type, day, room_number,
substring(rest, 1, locate(',', rest) - 1),
substring(rest, locate(',', rest) + 1)
from cte
where locate(',', rest) > 0
),
all_times as (
select 1 time
union all select time + 1 from all_times where time < 13
)
select t.cid, t.sid, t.type, t.day, at.time, t.building, t.room_number
from all_times at
cross join mytable t
left join cte c on c.cid = t.cid and c.time = at.time
where c.cid is null
order by t.cid, at.time
Demo on DB Fiddle:
样本数据:
cid | sid | type | day | time | building | room_number
--: | --: | :------ | :-- | :---- | :------- | ----------:
1 | 1 | daytime | mon | 6,7,8 | sky | 507
2 | 2 | daytime | thu | 2,3,4 | nuri | 906
查询结果:
cid | sid | type | day | time | building | room_number
--: | --: | :------ | :-- | ---: | :------- | ----------:
1 | 1 | daytime | mon | 1 | sky | 507
1 | 1 | daytime | mon | 2 | sky | 507
1 | 1 | daytime | mon | 3 | sky | 507
1 | 1 | daytime | mon | 4 | sky | 507
1 | 1 | daytime | mon | 5 | sky | 507
1 | 1 | daytime | mon | 9 | sky | 507
1 | 1 | daytime | mon | 10 | sky | 507
1 | 1 | daytime | mon | 11 | sky | 507
1 | 1 | daytime | mon | 12 | sky | 507
1 | 1 | daytime | mon | 13 | sky | 507
2 | 2 | daytime | thu | 1 | nuri | 906
2 | 2 | daytime | thu | 5 | nuri | 906
2 | 2 | daytime | thu | 6 | nuri | 906
2 | 2 | daytime | thu | 7 | nuri | 906
2 | 2 | daytime | thu | 8 | nuri | 906
2 | 2 | daytime | thu | 9 | nuri | 906
2 | 2 | daytime | thu | 10 | nuri | 906
2 | 2 | daytime | thu | 11 | nuri | 906
2 | 2 | daytime | thu | 12 | nuri | 906
2 | 2 | daytime | thu | 13 | nuri | 906
只需在其前面加上insert指令即可轻松地将其转换为insert查询，例如:
You can easily turn this to an insert query by just preceding it with with an insert instruction, like:
insert into sometable(cid, sid, type, day, time, building, room_number)
with recursive ...
select ...
from ...
where ...
实际上，由于csv列表中的值列表是预定义的，因此可以很好地完成而无需递归.您可以将天数列表与表一起使用，并使用find_in_set()识别差距.好处是它可以在不支持追溯查询的MySQL/MariaDB版本上运行:
Actually, since the list of values in the csv list is pre-defined, this can very well be done without recursion. You can join the list of days with the table and use find_in_set() to identify the gaps. The upside is that it would work on versions of MySQL / MariaDB that do not support recusive queries:
select t.cid, t.sid, t.type, t.day, at.time, t.building, t.room_number
from (
select 1 time union all select 2 time 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
union all select 10 union all select 11 union all select 12 union all select 13
) at
cross join (
select distinct cid, sid, type, day, building, room_number
from mytable
) t
left join mytable t1 on t1.cid = t.cid and find_in_set(at.time, t1.time)
where t1.cid is null
order by t.cid, at.time
Demo
这篇关于如何将字符串中的定界列表解析为行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
