mysql - 如何使用 find_in_set 使两个字符串在目标或其他内容中匹配？

Question

我有以下 2 个表。这是架构 Sqlfiddle为了它。

Table 1    
    Transaction  Items        
-----------  -------------
T1           I1,I3,I7     
T2           I7,I2,I3     
T3           I1,I2,I3     
T4           I2,I3        
T5           I2,I3,I4,I5  

Table 2 
    Id  Items   
------  --------
     1  I1,I3   
     2  I1,I2   
     3  I2,I4   
     4  I2,I3   
     5  I4,I5 

我想要Table 3中的结果，就像Table2中的每条记录一样，例如第一行I1,I3它在中出现了多少次>每条记录中的表 1。它应该显示在 SOT 列中作为答案。这里第一个是 2。

Table 3
    Id  Items      SOT  
------  ------  --------
     1  I1,I3          2
     2  I1,I2          1
     3  I2,I4          1
     4  I2,I3          4
     5  I4,I5          1

你能为此给我一些建议吗？我想到了 find_in_set 但它仅适用于匹配 1 字符串。

Answer 1

作为演示，以下 SQL 将为您提供您想要的结果(我认为)，其中 Table2.Items 中最多有 100 个逗号分隔值。

正如你所看到的，读起来并不愉快，任何将来维护这个声明的人可能都会感到非常困惑。我不建议在实时代码中做这样的事情。

SELECT Id, COUNT(*)
FROM
(
    SELECT Transaction, anItemCount, ItemVal.Id, COUNT(anItem) AS aCount
    FROM
    (
        SELECT DISTINCT Id, SUBSTRING_INDEX(SUBSTRING_INDEX(Items, ',', AnInt), ',', -1) AS anItem
        FROM Table2,
        (
            SELECT 1 + Units.i + Tens.i * 10 as AnInt
            FROM
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Units,
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Tens
        ) Ints
    ) ItemVal
    INNER JOIN
    (
        SELECT Id, COUNT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(Items, ',', AnInt), ',', -1)) AS anItemCount
        FROM Table2,
        (
            SELECT 1 + Units.i + Tens.i * 10 as AnInt
            FROM
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Units,
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Tens
        ) Ints
        GROUP BY Id
    ) ItemCnt
    ON ItemVal.Id = ItemCnt.Id
    INNER JOIN Table1
    ON FIND_IN_SET(ItemVal.anItem, Table1.Items)
    GROUP BY Transaction, anItemCount, ItemVal.Id
    HAVING anItemCount = aCount
) Sub1
GROUP BY Id

如果 Table2.Items 仅包含 2 个值，则可以减少为:-

SELECT Id, COUNT(*)
FROM
(
    SELECT Table1.Transaction, ItemVal.Id, COUNT(anItem) AS aCount
    FROM
    (
        SELECT Id, SUBSTRING_INDEX(Items, ',', 1) AS anItem
        FROM Table2
        UNION
        SELECT Id, SUBSTRING_INDEX(Items, ',', -1) AS anItem
        FROM Table2
    ) ItemVal
    INNER JOIN Table1
    ON FIND_IN_SET(ItemVal.anItem, Table1.Items)
    GROUP BY Table1.Transaction, ItemVal.Id
    HAVING aCount = 2
) Sub1
GROUP BY Id;

当 Table2.Items 中只有 2 个值时，也可以简单地完成此操作:-

SELECT Table2.Id, COUNT(Table1.Transaction) AS aCount
FROM Table2 
INNER JOIN Table1
ON FIND_IN_SET(SUBSTRING_INDEX(Table2.Items, ',', 1), Table1.Items)
AND FIND_IN_SET(SUBSTRING_INDEX(Table2.Items, ',', -1), Table1.Items)
GROUP BY Table2.Id

但仍然不太令人愉快。

这里是 SQL Fiddle:-

http://www.sqlfiddle.com/#!2/03fe9/19