sql - 总和值为空时SQL返回行

Question

我有两张桌子。一个带有物料清单，一个带有采购订单。现在我想显示产品的完整物料清单以及采购表中的订单总额。

**Billofmaterials**
BOM_ID   BOM_Prod_id     BOM_item_id
1        5               11
2        5               13
3        6               11
4        6               15
5        6               20

示例 prod_id（产品 id）6 有 3 个项目（id 11、15 和 20）。

**Purchasing**
PU_ID   PU_item_id     PU_amount     PU_status
1       11             100           On order
2       11             650           On order
3       11             40            Received
4       20             600           On order
5       8              10            On order
6       15             150           Received

现在我得到了以下 SQL

SELECT 
BOM_item_id,
SUM(DISTINCT purchasing.PU_amount) as total_on_order
FROM Billofmaterials
LEFT JOIN purchasing
ON Billofmaterials.BOM_item_id= purchasing.PU_item_id
AND purchasing.PU_status != 'Received'
AND BOM_prod_id = 6
GROUP BY BOM_item_id

此查询返回以下内容：

**Query result**
BOM_item_id   total_on_order
11            750             
20            600                       

因为只收到一个 BOM_item_id 15 的采购订单，所以它不返回值。现在我也想重新调整 BOM_item_id 15，但将 total_on_order 设为 0，如：

**Expected result**
BOM_item_id   total_on_order
11            750        
15            0      
20            600                       

我需要使用什么 SQL 特性/函数来获得预期的结果？

标签： sql

Answer 1

你可以试试下面的 -

SELECT BOM_item_id,coalesce(total_on_order,0) as total_on_order
FROM Billofmaterials left join
(
select PU_item_id,SUM(purchasing.PU_amount) as total_on_order
from purchasing
where purchasing.PU_status != 'Received'
group by PU_item_id
) purchasing
ON Billofmaterials.BOM_item_id= purchasing.PU_item_id
where BOM_prod_id = 6