qrying DB在一个订单中有两个客户

Question

假设我有两个表，订单order_id (PK( ordered_date

客户订单Customer_order_id (PK( order_id (FK( customer_type(字符 1( ( 可以是 S、T 和 M(

如果订单中涉及一个或多个不同类型的客户，则表格将如下所示订购order_id 5 order_date '05-06-2020'

客户订单customer_order_id 1 order_id 5 类型"M">

customer_order_id 2 order_id 5 类型 'S'

等等

如何编写一个将返回具有 S 和 M 类型客户组合的所有唯一order_ids的 qry？

Answer 1

很容易自连接查询：

SELECT DISTINCT M.order_id
FROM CustomerOrders AS M
INNER JOIN CustomerOrders AS S
ON M.order_id = S.order_id
WHERE M.customer_type = 'M'
AND S.customer_type = 'S'

Answer 2

您可以使用存在：

Select distinct order_id 
from CustomerOrder co
where exists (select * from CustomerOrder co1 
where co.order_id = co1.order_id and co1.Type = 'M') and
exists (select * from CustomerOrder co1 
where co.order_id = co1.order_id and co1.Type = 'S');