SQLite- 规范化连接的字段并加入它? [英] SQLite- Normalizing a concatenated field and joining with it?

问题描述

我在一个字段中以逗号分隔值存储了一些数据，我想将这些逗号分隔值转换为临时表并使用它们连接到另一个表

I have some data stored in comma-separated values in a field and I want to turn those comma-separated values into a temporary table and use those to join to another table

CREATE TABLE STRATEGY (STRATEGY_ID INTEGER PRIMARY KEY, APPLIED_SET_IDS VARCHAR);

CREATE TABLE ACTION_SET (APPLIED_ACTION_SET_ID INTEGER PRIMARY KEY, VALUE VARCHAR);

+-----------+---------------+
|STRATEGY_ID|APPLIED_SET_IDS|
+-----------+---------------+
|          1|1,3,6,7        |
|          2|1,2,4          |

+---------------------+-----+
|APPLIED_ACTION_SET_ID|VALUE|
+---------------------+-----+
|                    1|X    |
|                    2|Y    |
|                    3|Z    |
|                    4|H    |
|                    5|I    |
|                    6|J    |
|                    7|K    |
|                    8|L    |

我知道我必须使用某种形式的递归如此处所示.但我所做的每一次尝试都让我头晕目眩.而且我的临时表也需要保留 APPLIED_SET_ID 值的原始串联顺序，就像这样...

I know I have to use some form of recursion as shown here. But every attempt I've done has made my head spin a bit. And my temporary table needs to preserve the original concatenated order of APPLIED_SET_ID values as well, like this...

+-----------+-----+--------------+
|STRATEGY_ID|ORDER|APPLIED_SET_ID|
+-----------+-----+--------------+
|          1|    1|             1|
|          1|    2|             3|
|          1|    3|             6|
|          1|    4|             7|
|          2|    1|             1|
|          2|    2|             2|
|          2|    3|             4|

最终，我会将这个表加入到第二个现有表中，并使用 GROUP_CONCAT 以相同的顺序用相应的值替换 ID.

Ultimately, I will join this table to the second existing table and use GROUP_CONCAT to replace the ID's with the corresponding values in the same order.

+-----------+------------------+
|STRATEGY_ID|APPLIED_SET_VALUES|
+-----------+------------------+
|          1|X,Z,J,K           |
|          2|X,Y,H             |

正则表达式由于顺序而被淘汰(否则我可以将逗号转换为管道并加入REGEXP 语句).我怎样才能做到这一点?我知道这不是规范化的，但我需要使用当前的结构.提前感谢您的任何帮助.

So regular expressions are out thanks to the order (otherwise I could have turned the commas to pipes and joined on a REGEXP statement). How can I achieve this? I know this is not normalized but I need to work with this current structure. Thank you for any help in advance.

推荐答案

我的同事开发了一个非常聪明的解决方案，假设分隔符是管道 | 而不是逗号 ,.

My colleague developed a very clever solution, assuming the separator is a pipe | and not a comma ,.

他使用REGEXP 和INSTR() 函数来获取数字位置，然后该值驱动排序.

He used REGEXP and the INSTR() function to get a numerical position, and that value drove the sorting.

SELECT STRATEGY_ID,
APPLIED_SET_IDS,
GROUP_CONCAT(VALUE,'|') as DESCRIPTION 

FROM ( 
    SELECT STRATEGY_ID,
    APPLIED_SET_IDS,
    CASE 
        WHEN APPLIED_ACTION_SET_ID = APPLIED_SET_IDS THEN 1
        WHEN instr(APPLIED_SET_IDS, APPLIED_ACTION_SET_ID || '|') = 1 Then 1
        WHEN instr(APPLIED_SET_IDS, '|' || APPLIED_ACTION_SET_ID || '|') > 0 Then instr(APPLIED_SET_IDS, '|' || APPLIED_ACTION_SET_ID || '|')
        ELSE 999999
    END AS APPLIED_ORDER,
    VALUE

    FROM STRATEGY 
    INNER JOIN ACTION_SET
    ON ACTION_SET.APPLIED_ACTION_SET_ID REGEXP '^(' || STRATEGY.APPLIED_SET_IDS || ')$'

    ORDER BY APPLIED_ORDER
) DESCRIPTIONS

GROUP BY 1,2

这给了我我正在寻找的确切输出.

This gave me the exact output I was looking for.

这篇关于SQLite- 规范化连接的字段并加入它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！