SQL 否定查询

Question

我有三个表，我正在展示相同的简化版本

Customer_dim

[Cust_id] [Cust_Name]

Product_dim

[Prod_id] [Prod_Name]

订单_事实

[ord_id] [Cust_id] [Prod_id]

我想要所有购买过所有产品的客户(甚至一个都不缺)

我想要一个更优雅的查询，而不是简单地将每个客户组的计数与 prod_dim 的总数相等。

即我不想要以下类型的查询(因为这是一个面试问题，并且也有优雅的要点)

select cust_name 
from customers c, 
   (select cust_id, count(prod_id) cnt 
    from order_fact 
    group by cust_id where cnt = (select count(prod_id) from prod_dim)) t1
where c.cust_id = t1.cust_id

Answer 1

否 count或 group by版本:

SELECT Cust_Name FROM Customer_dim WHERE Cust_Id NOT IN (
    SELECT c.Cust_Id FROM Product_dim p
    CROSS JOIN Customer_dim c 
    LEFT JOIN Orders_fact o ON o.Prod_Id = p.Prod_Id AND c.Cust_Id = o.Cust_Id
    WHERE Ord_Id IS NULL
)