SQL实现自动汇总同medid列值并关联显示至关联表

完全不用手动逐个指定medid，我们可以通过分组聚合+关联查询来简化你的SQL，而且扩展性和性能都比现在的UNION写法好很多。

问题分析 #

你当前的写法需要为每个medid单独写一段查询再用UNION拼接，不仅繁琐，而且新增药品后还要手动修改SQL。我们可以先对tblmedstockin按medid分组计算总进货量，再把这个结果关联到目标表上。

解决方案SQL #

SELECT 
    a.medstockid,
    c.medid,
    c.category,
    c.medname,
    COALESCE(b.total_stockin, 0) AS stockinquantity,
    a.stockcritqty
FROM tblmedstock a
INNER JOIN tblmedicine c 
    ON a.medid = c.medid
LEFT JOIN (
    -- 先计算每个medid的总进货量
    SELECT 
        medid, 
        SUM(stockinquantity) AS total_stockin
    FROM tblmedstockin
    GROUP BY medid
) b 
    ON a.medid = b.medid

语句解释 #

1. 子查询b：先对tblmedstockin按medid分组，用SUM()计算每个药品的总进货量，这一步自动处理所有存在的medid，不用手动指定。
2. 关联查询：
   • 用INNER JOIN关联tblmedstock和tblmedicine，确保能拿到药品的分类和名称信息。
   • 用LEFT JOIN关联子查询b，这样即使某个药品没有任何进货记录（比如新入库的药品还没录进货数据），也能在结果中显示，并且用COALESCE()把总进货量设为0（如果不需要显示这类药品，把LEFT JOIN改成INNER JOIN即可）。

验证结果 #

用你提供的测试数据，这个SQL会自动计算：

• medid=1001的总进货量：100+100+12=212
• medid=1004的总进货量：4+20=24
  完全符合你给出的预期结果，而且后续新增medid时，不需要修改SQL就能自动包含进去。

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