优化已取消订单的 SQL 查询

Question

这是我的表的子集：

orders:
 - order_id
 - customer_id

order_products:
 - order_id
 - order_product_id (unique key)
 - canceled

我想选择给定客户（customer_id）的所有订单（order_id），其中订单中的所有产品都被取消，而不仅仅是某些产品。有没有比这更优雅或更有效的方法：

select order_id from orders
where order_id in (
    select order_id from orders
    inner join order_products on orders.order_id = order_products.order_id
    where order_products.customer_id = 1234 and order_products.canceled = 1
)
and order_id not in (
    select order_id from orders
    inner join order_products on orders.order_id = order_products.order_id
    where order_products.customer_id = 1234 and order_products.canceled = 0
)

Answer 1

由于我们不知道数据库平台，这里有一个 ANSI 标准方法。请注意，这对模式没有任何假设（即已取消字段的数据类型、如何设置已取消标志（即“是”、1 等）），并且不使用特定于给定数据库平台的任何内容（这可能是如果您可以向我们提供您正在使用的平台和版本，则更有效的方法）：

select  op1.order_id
from    (
            select  op.order_id, cast( case when op.cancelled is not null then 1 else 0 end as tinyint) as is_cancelled
            from    #order_products op
        ) op1
group by op1.order_id
having  count(*) = sum(op1.is_cancelled);

Answer 2

如果所有订单在 order_products 中至少有一行，请尝试一下

 Select order_id from orders o
 Where Not Exists 
      (Select * From order_products 
       Where order_id = o.order_id
          And cancelled = 1)

如果上述假设不成立，那么您还需要：

 Select order_id from orders o
 Where Exists 
      (Select * From order_products
       Where order_id = o.order_id)
   And Not Exists 
      (Select * From order_products
       Where order_id = o.order_id
          And cancelled = 1)

Answer 3

最快的方法是这样的：

SELECT  order_id
FROM    orders o
WHERE   customer_id = 1234
        AND
        (
        SELECT  canceled
        FROM    order_products op
        WHERE   op.order_id = o.order_id
        ORDER BY
                canceled DESC
        LIMIT 1
        ) = 0

当且仅当存在一些产品并且它们全部被取消时，子查询才会返回 0。

如果根本没有产品，子查询将返回NULL；如果至少有一个未取消的产品，子查询将返回1。

确保您在 order_products（order_id，已取消） 上有索引

Answer 4

像这样的东西吗？这假设每个订单至少有一种产品，否则此查询还将返回没有任何产品的订单。

 select order_id 
 from orders o
 where not exists (select 1 from order_products op
                   where canceled = 0
                   and op.order_id = o.order_id
 )
 and o.customer_id = 1234

Answer 5

SELECT customer_id, order_id, count(*) AS product_count, sum(canceled) AS canceled_count
FROM orders JOIN order_products
ON orders.order_id = order_products.order_id
WHERE customer_id = <<VALUE>>
GROUP BY customer_id, order_id
HAVING product_count = canceled_count

Answer 6

你可以尝试这样的事情

select  orders.order_id 
from    @orders orders inner join 
        @order_products order_products on orders.order_id = order_products.order_id
where   order_products.customer_id = 1234 
GROUP BY orders.order_id
HAVING SUM(order_products.canceled) = COUNT(order_products.canceled)