POS系统MySQL查询需求：筛选库存不足产品并关联同描述产品

核心解决方案 #

要满足「筛选符合库存条件的产品，同时包含所有同描述产品（含无库存记录的）」的需求，需要先锁定符合条件的产品描述集合，再关联全量产品数据，具体SQL语句如下：

兼容低版本MySQL的子查询写法

SELECT 
    p.UPC,
    p.Description,
    p.Price,
    s.Quantity,
    s.Max,
    s.Min,
    s.Ordered_Flag
FROM tblProducts p
LEFT JOIN tblStock s ON p.UPC = s.UPC
WHERE p.Description IN (
    -- 先筛选出所有符合库存条件的产品描述
    SELECT DISTINCT p_inner.Description
    FROM tblProducts p_inner
    INNER JOIN tblStock s_inner ON p_inner.UPC = s_inner.UPC
    WHERE s_inner.Quantity < s_inner.Max 
      AND s_inner.Ordered_Flag = 0
)
ORDER BY p.Description, p.UPC;

MySQL 8.0+ 推荐的CTE写法（可读性更强）

WITH EligibleDescriptions AS (
    SELECT DISTINCT p.Description
    FROM tblProducts p
    INNER JOIN tblStock s ON p.UPC = s.UPC
    WHERE s.Quantity < s.Max 
      AND s.Ordered_Flag = 0
)
SELECT 
    p.UPC,
    p.Description,
    p.Price,
    s.Quantity,
    s.Max,
    s.Min,
    s.Ordered_Flag
FROM tblProducts p
LEFT JOIN tblStock s ON p.UPC = s.UPC
WHERE p.Description IN (SELECT Description FROM EligibleDescriptions)
ORDER BY p.Description, p.UPC;

关键逻辑说明 #

• 子查询/CTE的作用：通过INNER JOIN精准筛选出满足Quantity < Max且Ordered_Flag=0的产品，再用DISTINCT去重得到唯一的描述集合，避免重复匹配
• LEFT JOIN tblStock：替代原来的INNER JOIN，确保没有库存记录的产品（如ID5）也能被查询到，这类产品的库存字段（Quantity、Max等）会显示为NULL
• WHERE条件过滤：只保留属于上述描述集合的产品，实现「只要同描述中有一个产品符合条件，全部同描述产品都显示」的需求

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