SQL：多对多关系，IN条件

Question

我有一个名为 transactions 的表，通过 items_transactions 表与项目具有多对多关系。

我想做这样的事情：

SELECT "transactions".* 
  FROM "transactions" 
INNER JOIN "items_transactions" 
        ON "items_transactions".transaction_id = "transactions".id 
INNER JOIN "items" 
        ON "items".id = "items_transactions".item_id 
WHERE (items.id IN (<list of items>))

但这为我提供了与列表中的一个或多个项目相关联的所有交易，并且我只希望它为我提供与所有这些项目相关联的交易。

任何帮助将不胜感激。

Answer 1

您必须扩展对列表中所有项目的查询：

SELECT "transactions".* 
FROM "transactions" 
WHERE EXISTS (SELECT 1 FROM "items_transactions"
              INNER JOIN "items" ON "items".id = "items_transactions".item_id 
              WHERE "items_transactions".transaction_id = "transactions".id
              AND "items".id = <first item in list>)
AND   EXISTS (SELECT 1 FROM "items_transactions"
              INNER JOIN "items" ON "items".id = "items_transactions".item_id 
              WHERE "items_transactions".transaction_id = "transactions".id
              AND "items".id = <second item in list>)
...

您也许还可以使用 IN 和 COUNT DISTINCT 进行处理，我不确定这样会更快。像这样的东西（完全未经测试）：

SELECT "transactions".* 
FROM "transactions" 
INNER JOIN (SELECT "items_transactions".transaction_id 
            FROM "items_transactions"
            INNER JOIN "items" ON "items".id = "items_transactions".item_id 
            WHERE "items".id IN (<list of items>)
            GROUP BY "items_transactions".transaction_id
            HAVING COUNT(DISTINCT "items".id) = <count of items in list>) matches ON transactions.transaction_id = matches.transaction_id

Answer 2

我想这就是你想要的。

我会将您需要的项目列表放入一张表中（临时一个就可以了）并加入其中。然后计算不同商品的数量，并将该计数与商品交易计数进行匹配。

我已经提供了示例 DDL &我用过的数据。

Create table #trans
(
transId int identity(1,1),
trans varchar(10)
)

Create Table #itemTrans
(
transId int,
itemId int
)

Create table #items
(
itemId int identity(1,1),
item varchar(10)
)

Create table #itemsToSelect
(
itemId int
)


Insert Into #trans
Values ('Trans 1')

Insert Into #trans
Values ('Trans 2')

Insert Into #trans
Values ('Trans 3')


Insert Into #Items
Values ('Item 1')

Insert Into #Items
Values ('Item 2')

Insert Into #Items
Values ('Item 3')

Insert Into #Items
Values ('Item 4')

Insert Into #itemTrans
Values (1, 1)

Insert Into #itemTrans
Values (1, 2)

Insert Into #itemTrans
Values (1, 3)

Insert Into #itemTrans
Values (2, 1)

Insert Into #itemTrans
Values (2, 3)

Insert Into #itemTrans
Values (3, 4)



Insert Into #itemsToSelect
Values (1)
Insert Into #itemsToSelect
Values (2)
Insert Into #itemsToSelect
Values (3)


Select t.transId

From #items i 
Join #itemTrans it on i.itemId = it.itemId
Join #trans t on it.transId = t.transId

Join #itemsToSelect its on it.ItemId = its.ItemId

Where it.TransId is not null
Group by t.transId
Having count(distinct(it.itemId)) = (Select count(distinct(itemId)) from #itemsToSelect)

Answer 3

SELECT transactions.*
WHERE (SELECT count(*)
       FROM items_transactions
       WHERE items_transactions.transaction_id = transactions.transaction_id
             AND items_transactions.item_id IN (<list of items>)
      ) = <number of items>

虽然这可能会扫描事务，但为每个事务嵌套相关子查询...不是特别有效，所以也许：

SELECT transactions.*
WHERE EXISTS (SELECT 1 FROM items_transactions
              WHERE items_transactions.transaction_id = transactions.transaction_id
              AND items_transactions.item_id IN (<list of items>)
      )
      AND
      (SELECT count(*)
       FROM items_transactions
       WHERE items_transactions.transaction_id = transactions.transaction_id
             AND items_transactions.item_id IN (<list of items>)
      ) = <number of items>

或者类似的方法说服数据库首先找到与至少一个项目相关的事务，然后检查每笔交易都与以后的所有项目相关联。

正如有人指出的，您也可以简单地为每个项目生成连接子句，如果项目数量不大，这可能会更好。

Answer 4

我还没有执行这个，但这应该会给你你想要的结果：

SELECT t.* FROM items i
    INNER JOIN items_transactions it ON i.id = it.item_id
        INNER JOIN transactions t ON it.transaction_id = t.id
WHERE i.id IN (1,2,3)

Answer 5

查询的最后一点看起来是错误的：

WHERE (items.id IN (<list of items>))

“in”语句就像一个大的 OR 语句而不是 AND 语句，因此优化器将其扩展为：

WHERE (items.id = 123 OR items.id = 456 OR items.id = 789)

编辑

我认为您需要执行项目表上的相关子查询。