如何将字符串中的分隔列表解析为行？

这是我之前的问题：如何在mysql中合并两个逗号分隔的数字字符串？
我尝试使用分隔列表，原因如下：
数据超过2000行。
我只有一天。
这不是生产水平
我需要用手分析数据。
我想我别无选择。但我发现：sql将值拆分为多行
所以，我从中得到了一些希望。但在我的table上很难用。
我的不一样。我有多个列，我想避免重复行。
我宁愿更新和插入而不是选择，因为用这个方法更改表的形式将更容易管理reason:is storing 数据库列中的分隔列表真的那么糟糕吗？
我需要找到未列出的号码。例如，如果时间值为1、3、8，则需要2、4、5、6、7、9、10、11、12、13（介于1和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 |努里大厦的日程安排在周四、周二。那是不同的一天。所以，它们不会相互影响。所以，星期四是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         |
+-----+------+--------+------+-------+-----------+-------------+

我这样做的原因是源数据是类的时间表。我想找些空的时间，这样人们可以在上课的时候使用教室。

下面是一种使用递归查询将字符串拆分为行的方法。然后，您可以使用这个结果集来识别丢失的记录：为此，您可以 cross join 原表带有时间列表，并使用反- left join 要提取丢失的记录：

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

db小提琴演示：
样本数据：

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 指令，比如：

insert into sometable(cid, sid, type, day, time, building, room_number)
with recursive ...
select ...
from ...
where ...

实际上，由于csv列表中的值列表是预先定义的，因此不需要递归就可以很好地完成。您可以将日期列表与表合并并使用 find_in_set() 找出差距。好处是它可以在不支持递归查询的mysql/mariadb版本上工作：

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

演示

如何将字符串中的分隔列表解析为行？

1条答案

相关问题

热门标签

最新问答