多表关联实现商品数量转换查询需求及SQL语句验证

现有数据表结构 #

tbl_goods（商品销售表） #

+--------+-------+-------+-------+  
| goods  | code  | qty   | unit  |  
+--------+-------+-------+-------+   
| cigar  | G001  | 1     | pack  |
| cigar  | G001  | 2     | pcs   |
| bread  | G002  | 2     | pcs   |   
| soap   | G003  | 1     | pcs   |  
+--------+-------+-------+-------+

tbl_units（单位转换表） #

注：tbl_units的code字段前加"K"以避免与tbl_goods的code冲突。

+--------+-------------+-------+  
| code   | ucode       | qty   |
+--------+-------------+-------+
| KG001  | U001        | 10    |
+--------+-------------+-------+

tbl_sat（单位对照表） #

+--------+-------------+ 
| ucode  | unit        |
+--------+-------------+
| U001   | pack        |
+--------+-------------+
| U002   | box         |
+--------+-------------+
| U003   | crate       | etc

查询需求 #

查询时，若tbl_goods的code在tbl_units中有匹配项（拼接"K"后），则根据对应单位转换数量；无匹配项则直接显示原qty，期望结果如下：

+--------+-------+-------+-------+--------+  
| goods  | code  | qty   | unit  | total  |
+--------+-------+-------+-------+--------+   
| cigar  | G001  | 1     | pack  | 10     |
| cigar  | G001  | 2     | pcs   | 2      |
| bread  | G002  | 2     | pcs   | 2      |
| soap   | G003  | 1     | pcs   | 1      |
+--------+-------+-------+--------+

待验证的SQL语句 #

本人基于@danielpr的查询语句修改后得到以下SQL，请求验证是否可行：

SELECT j.code,j.qty ,j.unit, IIF(j.unit=t.unit,j.qty*u.qty,j.fqty)  FROM tbl_goods j
LEFT JOIN tbl_units u on u.code ='K' || j.code 
LEFT JOIN tbl_sat t ON t.ucode =u.ucode  [WHERE j.code='G001']
GROUP BY j.code,j.qty 

注：[WHERE ..]为可选条件，用于筛选特定商品。

验证结果与修正建议 #

你的SQL存在多个问题，无法得到期望结果：

1. 字段错误：j.fqty是不存在的字段，无匹配时应返回原数量j.qty。
2. 遗漏核心字段：期望结果包含goods字段，但你的SELECT语句未选取该字段。
3. 冗余GROUP BY：当前场景没有聚合操作，GROUP BY会导致同code、qty的记录被合并，丢失原始数据（比如cigar的两条记录会被合并）。
4. NULL值隐患：当无匹配转换关系时，u.qty为NULL，此时j.qty*u.qty会得到NULL，需要处理避免异常值。

修正后的SQL如下：

SELECT 
    j.goods,
    j.code,
    j.qty,
    j.unit,
    IIF(j.unit = t.unit, j.qty * COALESCE(u.qty, 1), j.qty) AS total
FROM tbl_goods j
LEFT JOIN tbl_units u ON u.code = 'K' || j.code
LEFT JOIN tbl_sat t ON t.ucode = u.ucode
-- WHERE j.code = 'G001' -- 可选条件，按需启用

修正说明 #

• 补充goods字段，与期望结果列名一致。
• 替换j.fqty为j.qty，修正不存在字段的问题。
• 用COALESCE(u.qty, 1)确保无匹配转换数量时，相乘结果等于原qty，避免NULL值。
• 移除不必要的GROUP BY，保证每条原始记录正常显示。
• 给计算字段命名为total，匹配期望结果的列名。

执行这条修正后的SQL可以得到你想要的结果：

• cigar的pack单位匹配到转换关系，计算1*10=10；
• 其他无匹配的记录直接返回原qty值。

内容的提问来源于stack exchange，提问作者Imam