sql - SELECT 语句检索从未发生过的事情

Question

我有包含这些列的 CallHistory 表:

CustomerId, TimeOfCall, CallResult

并且在 CallResult 我可能有如下值:“已下订单”、“要求返回”、“技术问题”、“询价”等

我想SELECT CustomerId FROM CallHistory WHERE 客户至少有一次“询问定价”但从未“下订单”。

谢谢!

Answer 1

SELECT DISTINCT c.CustomerId
    FROM CallHistory c
    WHERE c.CallResult = 'inquired about Pricing'
        AND NOT EXISTS(SELECT 1
                           FROM CallHistory c2
                           WHERE c2.CustomerId = c.CustomerId
                               AND c2.CallResult = 'Placed Order')