多表关联查询库存状态异常：如何正确标记OUT_OF_STOCK与IN_STOCK

原查询的问题在于：使用GROUP BY a.pid但SELECT中包含了未聚合的b.did、c.t_stock等字段，数据库会随机选取分组内某一条记录的t_stock值来判断状态，导致只要分组内存在一条t_stock=0的记录，就会错误标记为OUT_OF_STOCK，忽略了同pid下其他有库存的设计。

要实现正确的库存状态判断，需要先按pid聚合，判断该商品下是否存在非0库存的设计，再关联获取商品和设计的详细信息。以下是两种可行的修正方案：

方案一：使用子查询预计算每个pid的库存状态 #

SELECT 
    a.pid, 
    b.did, 
    a.p_name, 
    a.discount_price, 
    a.original_price, 
    a.p_description, 
    a.p_viewable, 
    c.t_stock, 
    c.t_name,
    CASE 
        WHEN pid_stock.has_stock = 1 THEN 'IN_STOCK' 
        ELSE 'OUT_OF_STOCK' 
    END AS stock_status
FROM product a
JOIN product_design b ON a.pid = b.pid
JOIN design_type c ON c.did = b.did
JOIN (
    -- 子查询：判断每个pid是否存在非0库存的设计
    SELECT 
        p.pid,
        MAX(CASE WHEN dt.t_stock > 0 THEN 1 ELSE 0 END) AS has_stock
    FROM product p
    JOIN product_design pd ON p.pid = pd.pid
    JOIN design_type dt ON pd.did = dt.did
    WHERE p.p_viewable = 'Y'
    GROUP BY p.pid
) pid_stock ON a.pid = pid_stock.pid
WHERE a.p_viewable = 'Y';

方案二：使用窗口函数直接计算每个pid的库存状态 #

如果你的数据库支持窗口函数（如MySQL 8.0+、PostgreSQL、SQL Server等），可以用更简洁的写法：

SELECT 
    a.pid, 
    b.did, 
    a.p_name, 
    a.discount_price, 
    a.original_price, 
    a.p_description, 
    a.p_viewable, 
    c.t_stock, 
    c.t_name,
    CASE 
        WHEN MAX(CASE WHEN c.t_stock > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY a.pid) = 1 THEN 'IN_STOCK'
        ELSE 'OUT_OF_STOCK'
    END AS stock_status
FROM product a
JOIN product_design b ON a.pid = b.pid
JOIN design_type c ON c.did = b.did
WHERE a.p_viewable = 'Y';

关键逻辑说明 #

• 两种方案核心都是对每个pid进行全局判断：只要该pid下有任意一个设计的t_stock>0，就标记为IN_STOCK；只有当所有设计的t_stock都为0时，才标记为OUT_OF_STOCK。
• 避免了原查询中随机选取单条记录t_stock的问题，确保状态判断基于该商品下所有设计的库存情况。

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