mysql - 如何选择相同 `userid` 其他字段具有特定值的行？

Question

我有这样的表格(简化):

orders           sample data below
---------------------------------------------
id INT:          1     2     3     4     5
userid INT       10    10    10    20    20
status CHAR(1)   A     A     B     A     C

并且想要选择每个 userid 状态为 IN ('A','B') 的所有订单，但根本没有订单 IN ( 'C'，'D')。

因此，上述数据的输出将给出 ID=1、2 和 3 的订单。用户 ID=10 有订单 A 和 B，但没有订单 C 或 D。

换句话说:为订单状态为 A 或 B，但没有状态 C 或 D 的客户选择订单。

我从这个开始:

SELECT 
    xcart_orders.orderid, 
    xcart_orders.*          
    FROM xcart_orders 
    JOIN (
        select count(*) as bad_statuses, userid from xcart_orders 
        where status in ('C','D') 
        group by userid 
    ) bo 
    ON bo.userid=xcart_orders.userid 
    JOIN (
        select count(*) as good_statuses, userid from xcart_orders 
        where status in ('A','B') 
        group by userid 
    ) bo2 
    ON bo2.userid=xcart_orders.userid 
    WHERE bo2.good_statuses>0 and bo.bad_statuses=0  

但认为 count(*) 不会针对“坏”状态返回零，因此我没有得到任何结果。

Answer 1

您有一个没有 GROUP BY 的聚合，为了检查结果，您需要我们 HAVING 而不是 WHERE

SELECT 
    xcart_orders.orderid, 
    xcart_orders.*, 
    SUM(CASE WHEN xcart_orders.status in ('C','D') THEN 1 ELSE 0 END) AS bad_statuses, 
    SUM(CASE WHEN xcart_orders.status in ('A','B') THEN 1 ELSE 0 END) AS good_statuses 
FROM xcart_orders 
GROUP BY orderid
HAVING bad_statuses = 0 
   AND good_statuses > 0 

请注意，您从 xcart_orders.* 获得的字段将是随机的(或非确定性的)，如果您需要特定的字段，则需要先订购它。

• 首先，您按 user_id 进行分组，检查是否有任何与 'A'、'B' 不同的状态

• 然后您可以从这些 user_id 中选择订单:

SQL DEMO

SELECT `user_id`
FROM orders1
GROUP BY `user_id`
HAVING COUNT(*) = COUNT(CASE WHEN `status` IN ('A', 'B') THEN 1 END);


SELECT *
FROM orders1
WHERE `user_id` IN (SELECT `user_id`
                    FROM orders1
                    GROUP BY `user_id`
                    HAVING COUNT(*) = COUNT(CASE WHEN `status` IN ('A', 'B') THEN 1 END)
                   );

输出