图书借阅库SQL查询需求：隐藏无库存/已借完的商品记录

原SQL的核心问题 #

1. 关联方式错误：使用INNER JOIN只会返回有借出记录的商品，既忽略了指定日期未被借出但库存充足的商品，还会错误筛选出库存为0但有借出记录的商品。
2. GROUP BY逻辑违规：在GROUP BY中使用计算列别名Lend_Total不符合SQL标准（多数数据库不支持），且分组逻辑冗余。
3. 筛选条件完全颠倒：HAVING Lend_Total < 1是在筛选剩余数量≤0的商品，和你要展示可借阅（剩余数量>0）商品的需求完全相反。

修正后的SQL语句 #

SELECT 
    p.Product_ID,
    p.Product_QTY - COALESCE(l.Lend_Count, 0) AS Available_QTY
FROM 
    dbo.Products p
LEFT JOIN (
    -- 先单独统计指定日期内每个商品的借出总数
    SELECT 
        Product_ID,
        COUNT(Lend_ID) AS Lend_Count
    FROM 
        dbo.Lending
    WHERE 
        Date = '11/09/2022'
    GROUP BY 
        Product_ID
) l ON p.Product_ID = l.Product_ID
WHERE 
    p.Product_QTY > 0  -- 直接排除库存为0的商品
    AND p.Product_QTY - COALESCE(l.Lend_Count, 0) > 0  -- 确保剩余可借数量大于0
-- 若仅需查询特定商品（比如ID=18），添加以下条件
-- AND p.Product_ID = 18

关键逻辑说明 #

• 子查询预统计：先单独处理借出记录的统计，避免关联主表后出现重复计数的问题。
• LEFT JOIN + COALESCE：确保所有库存>0的商品都被纳入查询，对于指定日期无借出记录的商品，用COALESCE将借出数转为0，保证剩余数量计算不会出现NULL值。
• 双重过滤：先排除库存为0的商品，再筛选出剩余可借数量>0的记录，完全匹配你的需求。

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