SQLite多表关联查询：实现商品数量转换计算

表结构说明 #

tbl_goods（已售商品表） #

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

tbl_units表（单位转换规则表） #

注：为避免与tbl_goods的code冲突，此表code前缀添加了字母'K'

+--------+-------+-------+  
| code   | ucode | qty   |
+--------+-------+-------+
| KG001  | U001  | 10    |
| KG001  | U002  | 20    |
| KG002  | U001  | 15    |
+--------+-------+-------+

tbl_sat表（单位编码映射表） #

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

需求说明 #

仅cigar（对应code G001）和bread（对应code G002）有单位转换规则（在tbl_units中存在对应code），需生成包含total字段的结果集：

• 若商品单位存在转换规则，total = tbl_goods.qty * tbl_units.qty
• 若不存在转换规则（如soap的pcs单位），total = tbl_goods.qty

期望结果：

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

解决方案SQL #

SELECT 
    g.goods,
    g.code,
    g.qty,
    g.unit,
    COALESCE(g.qty * u.qty, g.qty) AS total
FROM tbl_goods g
LEFT JOIN tbl_sat s ON g.unit = s.unit
LEFT JOIN tbl_units u ON CONCAT('K', g.code) = u.code AND s.ucode = u.ucode
ORDER BY g.goods, g.code, g.unit;

代码说明 #

1. CONCAT('K', g.code)：将tbl_goods的code拼接前缀'K'，与tbl_units的code匹配
2. 左连接确保所有tbl_goods的记录都被保留
3. COALESCE(g.qty * u.qty, g.qty)：存在转换规则则计算乘积，否则直接取原qty作为total

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