SQL - 计算每个 Field-A 值的 Field-B 值集

Question

首先抱歉，我想不出更具描述性的标题。

我想要做的是仅使用 SQL 执行以下操作：

我有一些字符串列表，list1、list2 和 list3。

我有一个数据集，其中包含两个有趣的列：A 和 B。A 列包含 TransactionID，B 列包含 ItemID。

当然，可以有多行共享相同的 TransactionID。

我需要捕获每个列表中至少有一个 ItemID 的交易（list1 AND list2 AND list3）。

我还需要计算每笔交易发生了多少次。 [编辑] 也就是说，计算每个 TransactionID 有多少个完整的 ItemID 集”，“完整集”是 list1 的任何元素与 list2 的任何元素与 list3 的任何元素

我希望这有足够的意义，也许我将能够以清晰的头脑更好地解释它，

提前致谢。

Answer 1

在 MySQL 中，如果您有以下列表：

list1 = ('1', '3')
list2 = ('2', '3')
list3 = ('3', '5')

那么您可以执行以下操作：

SELECT
    TransactionID,
    SUM(ItemID IN ('1', '3')) AS list1_count,
    SUM(ItemID IN ('2', '3')) AS list2_count,
    SUM(ItemID IN ('3', '5')) AS list3_count
FROM table1
GROUP BY TransactionID
HAVING list1_count > 0 AND list2_count > 0 AND list3_count > 0

结果：

TransactionId  list1_count  list2_count  list3_count
1              3            2            1
3              2            2            1

测试数据：

CREATE TABLE table1 (ID INT NOT NULL, TransactionID INT NOT NULL, ItemID INT NOT NULL);
INSERT INTO table1 (ID, TransactionID, ItemID) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 1, 1),
(6, 2, 1),
(7, 2, 2),
(8, 2, 1),
(9, 2, 4),
(10, 3, 3),
(11, 3, 2),
(12, 3, 1);

Answer 2

根据您的方言，并假设您的列表是其他表......

SELECT
    TransactionID, Count1, Count2, Count3
FROM
    MyDataSet M
    JOIN
    (SELECT COUNT(*), ItemID AS Count1 FROM List1 GROUP BY ItemID) T1 ON T1.ItemID = M.ItemID
    JOIN
    (SELECT COUNT(*), ItemID AS Count2 FROM List2 GROUP BY ItemID) T2 ON T2.ItemID = M.ItemID
    JOIN
    (SELECT COUNT(*), ItemID AS Count3 FROM List3 GROUP BY ItemID) T3 ON T3.ItemID = M.ItemID

Answer 3

如果 list1、list2 和 list3 实际上是已知的枚举，您可以这样做：

SELECT TransactionID, COUNT(*)
FROM MyTable
WHERE ItemID IN (list1) AND ItemID IN (list2) AND ItemID IN (list3)
GROUP BY TransactionID

如果您有很多列表，您可能希望在程序中生成 SQL。然而，即使对于很多列表，它仍然应该表现得很好。将您期望匹配数最少的列表放在前面，以便您尽快停止评估谓词。

如果您的列表位于另一个表中，可能是一堆 (list_id, item_id) 形式的元组，那么这是一个更棘手的问题。在尝试提出查询之前，我想了解更多信息。