比较不同表中两列的SUM（）？

I have these two queries:

我有这两个问题：

-- Getting the total no. of items CLAIMED for a sales order
SELECT SUM(qty_claimed) As 'total_items_claimed'
FROM so_claim_item
WHERE sales_order_id = 1;

-- Getting the total no. of items originally ORDERED for a sales order
SELECT SUM(quantity) As 'total_items_ordered'
FROM sales_order_item
WHERE sales_order_id = 1;

-- If the sums of the two columns are equal, return 1 or true. 
-- Else, return 0 or false.

I need to compare the sums of those two columns from different tables to see if a sales order is complete or not.

我需要比较不同表中这两列的总和，看看销售订单是否完整。

If 1 is returned, meaning the no. of items claimed is equal to no. of items ordered, it means the sales order is completed. Otherwise, the sales order remains unclaimed/partially claimed.

如果返回1，则表示否。声称的物品等于否。订购的商品，表示销售订单已完成。否则，销售订单仍然无人认领/部分索赔。

This is by far the best solution I can think of, but I wish to write it in a single query that will only return one value, if that's possible. But if you have better suggestions, I'm all ears.

这是我能想到的最好的解决方案，但我希望在一个查询中编写它，只返回一个值，如果可能的话。但如果你有更好的建议，我会全力以赴。

3 个解决方案

#1

Just using CASE?

只是使用CASE？

SELECT CASE
       WHEN
       (
           SELECT SUM(qty_claimed) AS 'total_items_claimed'
           FROM so_claim_item
           WHERE sales_order_id = 1
       ) =
       (
           SELECT SUM(quantity) AS 'total_items_ordered'
           FROM sales_order_item
           WHERE sales_order_id = 1
       ) THEN
           1
       ELSE
           0
   END;

#2

Try to use having as below:

尝试使用如下：

    SELECT SUM(qty_claimed) As 'total_items_claimed'
    FROM so_claim_item A
    WHERE A.sales_order_id = 1    
    having SUM(qty_claimed) =(SELECT SUM(quantity) As 'total_items_ordered') 
                     FROM sales_order_item B
                     WHERE B.sales_order_id = 1)

Here is more informations about having.

这里有更多关于拥有的信息。

#3

I would recommend doing this by putting the values in the FROM clause:

我建议通过将值放在FROM子句中来执行此操作：

SELECT (sci.total_items_claimed = soi.total_items_ordered) as is_same
FROM (SELECT SUM(qty_claimed) As total_items_claimed
      FROM so_claim_item
      WHERE sales_order_id = 1
     ) sci CROSS JOIN
     (SELECT SUM(quantity) As total_items_ordered
      FROM sales_order_item
      WHERE sales_order_id = 1
     ) soi;

You can also do this with subqueries in the SELECT:

您也可以使用SELECT中的子查询执行此操作：

SELECT ( (SELECT SUM(qty_claimed) As total_items_claimed
          FROM so_claim_item
          WHERE sales_order_id = 1
         ) =
         (SELECT SUM(quantity) As total_items_ordered
          FROM sales_order_item
          WHERE sales_order_id = 1
         )
       ) as is_same;

#1