选择包含多于一件商品的所有订单并检查所有商品状态 [英] SELECT all orders with more than one item and check all items status

问题描述

我有 2 张桌子:

订单:

Id   |  Status
-----+--------
 1   |      OK
 2   | WAITING
 3   | WAITING
 4   |      OK
 5   |      OK
 6   |      OK

和 Order_details:

And Order_details:

Id   |  Order_Id  |  Status
-----+------------+--------
 1   |         1  |      S1
 2   |         1  |      S1
 3   |         2  |      S1
 4   |         4  |      S2
 5   |         4  |      S2
 6   |         4  |      S3
 7   |         5  |      S1
 8   |         5  |      S1

我需要做的是选择所有具有多个 Order_detail 且订单状态必须为OK"的订单，并且每个 order_detail 必须在 (S1,S2) 中有状态

What I need to do is to select all orders having more than one Order_detail and Order status must be 'OK' and every order_detail must have status in (S1,S2)

我做过这样的事情:

SELECT O.Id FROM Orders O 
            JOIN Order_details OD 
                ON O.Id=OD.Order_Id
WHERE O.Status='OK' AND OD.Status IN ('S1','S2')
GROUP BY O.Id
HAVING count(DISTINCT OD.Id)>1

但这会返回所有满足条件的 Order_details 的所有订单.

But this returns all Orders that have more than one Order_details meeting criteria.

我确定这部分没问题:

SELECT O.Id FROM Orders O 
            JOIN Order_details OD 
                ON O.Id=OD.Order_Id
WHERE O.Status='OK'
GROUP BY O.Id
HAVING count(DISTINCT OD.Id)>1

但我必须检查上述订单的每个 Order_detail 是否都有状态 IN (S1, S2).

But I must check if every Order_detail for above orders has Status IN (S1, S2).

因此，如果订单将有 2 个详细信息，其中一个具有 status=S1 和第二个 S3，则应跳过此订单.

So if Order will have 2 details and one of them has status=S1 and second S3 this order should be skipped.

推荐答案

SELECT O.Id 
FROM Orders O 
JOIN Order_details OD ON O.Id=OD.Order_Id
WHERE O.Status='OK'
GROUP BY O.Id
HAVING count(DISTINCT OD.Id)>1
AND SUM(CASE WHEN OD.Status NOT IN ('S1','S2') 
             THEN 1 
             ELSE 0 
        END) = 0

这篇关于选择包含多于一件商品的所有订单并检查所有商品状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！