基于子表的计算摘要字段

I have two tables, Order and OrderItem. There is a one-to-many relationship on Order.Order_ID=OrderItem.Order_ID

我有两个表,Order和OrderItem。 Order.Order_ID = OrderItem.Order_ID上存在一对多关系

I want a query to return a list showing the status of each Order, COMPLETE or INCOMPLETE.

我想要一个查询返回一个列表,显示每个Order,COMPLETE或INCOMPLETE的状态。

A COMPLETE Order is defined as one where all the related OrderItem records have a non-NULL, non-empty value in the OrderItem.Delivery_ID field.

COMPLETE Order定义为OrderItem.Delivery_ID字段中所有相关OrderItem记录都具有非NULL非空值的订单。

This is what I have so far:

这是我到目前为止:

  SELECT Order.Order_ID, 'INCOMPLETE' AS Order_status
  FROM Order 
  WHERE EXISTS
    (SELECT *
     FROM OrderItem 
     WHERE OrderItem.Order_ID=Order.Order_ID 
     AND (OrderItem.Delivery_ID IS NULL OR OrderItem.Delivery_ID=''))
UNION 
  SELECT Order.Order_ID, 'COMPLETE' AS Order_status
  FROM Order 
  WHERE NOT EXISTS
    (SELECT *
     FROM OrderItem 
     WHERE OrderItem.Order_ID=Order.Order_ID 
     AND (OrderItem.Delivery_ID IS NULL OR OrderItem.Delivery_ID=''))
ORDER BY Order_ID DESC

It works, but runs a bit slow. Is there a better way?

它有效,但运行有点慢。有没有更好的办法?

(N.B. I've restated the problem for clarity, actual table and field names are different)

(N.B.我为了清晰起见重述了这个问题,实际的表和字段名称是不同的)

3 个解决方案

#1

I would suggest you have a column status on your Order table and update the status to complete when all order items get delivered.

我建议您在Order表上有一个列状态,并在所有订单商品交付时更新状态以完成。

It will make simple your query to get status as well improve performance.

它将简化您的查询以获取状态并提高性能。

#2

Put it into a subquery to try to make the case statement less confusing:

将它放入子查询中以尝试使case语句不那么混乱:

SELECT Order_ID,
       CASE WHEN incomplete_count > 0 THEN 'INCOMPLETE' ELSE 'COMPLETE' END
           AS Order_status

  FROM ( SELECT o.Order_ID
               ,SUM( CASE WHEN OrderItem.Delivery_ID IS NULL OR OrderItem.Delivery_ID='' THEN 1 ELSE 0 END )
                 AS incomplete_count
           FROM Order o 
           INNER JOIN OrderItem i ON (i.Order_ID = o.Order_ID)
           GROUP by o.Order_ID
       ) x

   ORDER BY ORder_ID DESC

The idea is to keep a counter every time you encounter a null item. If the sum is 0, there were no empty order items.

这个想法是每次遇到空项目时都保留一个计数器。如果总和为0,则没有空订单项。

#3

Try this one -

试试这个 -

SELECT 
       o.Order_ID
     , Order_status = 
               CASE WHEN ot.Order_ID IS NULL 
                    THEN 'COMPLETE' 
                    ELSE 'INCOMPLETE' 
               END
FROM dbo.[Order] o
LEFT JOIN (
     SELECT DISTINCT ot.Order_ID
     FROM dbo.OrderItem ot
     WHERE ISNULL(ot.Delivery_ID, '') = '' 
) ot ON ot.Order_ID = o.Order_ID

#1