SELECT其中group包含来自另一个表的值(SELECT where group contains values from another table)

SELECT其中group包含来自另一个表的值(SELECT where group contains values from another table)

我有一个与订单 - 供应商关系的简单表。 它显示哪些供应商参与了哪个订单。 显然，一个订单可以有多个供应商：

+---------+------------+ | OrderId | SupplierId | +---------+------------+ | O_1 | S_1 | +---------+------------+ | O_1 | S_3 | +---------+------------+ | O_2 | S_1 | +---------+------------+ | O_2 | S_2 | +---------+------------+ | O_2 | S_999 | +---------+------------+

我有另一个表包含这样的供应商对（输入数据）：

+--------------+--------------+ | SupplierId_1 | SupplierId_2 | +--------------+--------------+ | S_1 | S_2 | +--------------+--------------+ | S_2 | S_3 | +--------------+--------------+

请注意，对是唯一的，因此如果有（S_1，S_2）对，则不会有（S_2，S_1）对。 但是不可能说出该对将会以何种顺序出现：它可以是（S_1，S_2）或（S_2，S_1），但不是两者。

如何获得这些配对供应商的订单ID？

例如，在这种情况下，将不会选择具有Id O_1的订单，因为没有（S_1，S_3）或（S_3，S_1）对，但是将选择O_2，因为其供应商中至少有两个具有一对（S_1和S_2） 。

所以基本上我需要按OrderId对第一个表进行分组，并选择订单，其中每个订单供应商列表包含来自第二个表的至少一对。

I have a plain table with order-supplier relations. It shows which suppliers participated in which order. Obviously one order can have multiple suppliers:

+---------+------------+ | OrderId | SupplierId | +---------+------------+ | O_1 | S_1 | +---------+------------+ | O_1 | S_3 | +---------+------------+ | O_2 | S_1 | +---------+------------+ | O_2 | S_2 | +---------+------------+ | O_2 | S_999 | +---------+------------+

And I have another table which contains pairs of suppliers like that (input data):

+--------------+--------------+ | SupplierId_1 | SupplierId_2 | +--------------+--------------+ | S_1 | S_2 | +--------------+--------------+ | S_2 | S_3 | +--------------+--------------+

Note that pairs are unique, so if there is (S_1, S_2) pair there won't be (S_2, S_1) pair. But it's impossible to say in which order the pair will come: it can come as (S_1, S_2) or as (S_2, S_1) but not both.

How can I get IDs of orders having suppliers from these pairs?

For example in this case order with Id O_1 won't be selected because there is no (S_1, S_3) or (S_3, S_1) pair but O_2 will be selected because at least two of its suppliers have a pair (S_1 and S_2).

So basically I need to group up the first table by OrderId and select orders where each order supplier list contains at least one pair from the second table.

最满意答案

一种方法使用两个连接：

select o1.orderid, p.supplierid_1, p.supplierid_2 from pairs p join orders o1 on o1.supplierid = p.supplierid_1 join orders o2 on o2.supplierid = p.supplierid_2 and o2.orderid = o.orderid;

这颠倒了问题中的逻辑。 首先考虑对，以及哪些对与每个供应商具有相同的顺序。

One method uses two joins:

select o1.orderid, p.supplierid_1, p.supplierid_2 from pairs p join orders o1 on o1.supplierid = p.supplierid_1 join orders o2 on o2.supplierid = p.supplierid_2 and o2.orderid = o.orderid;

This inverts the logic in the question. Think about the pairs first, and which pairs have the same order with each supplier.