MySQL实现图书剩余数量查询：求正确SQL语句写法指导

需求：查询BookID及图书馆中对应图书的剩余数量，计算公式为：剩余数量 = 总馆藏数（NumOfCopy） - 已借出未归还的图书数量。

原错误代码 #

Select lms.books.bookid,
       (lms.books.numofcopy-1 where giveback = 0) as numberofbookremain
From lms.books 
join lms.receipts On lms.books.bookid = lms.receipts.bookid

错误分析 #

• 语法错误：(lms.books.numofcopy-1 where giveback = 0)不符合SQL语法规则，不能直接在字段计算中添加WHERE条件
• 数据缺失：使用普通JOIN会过滤掉没有借出记录的图书，导致这类图书不会出现在结果中
• 统计错误：固定减1而非统计每本图书的实际未归还数量，不符合需求

正确实现方案 #

方案一：左连接+分组聚合 #

SELECT 
    b.BookID,
    (b.NumOfCopy - COALESCE(COUNT(r.ReceiptID), 0)) AS NumberOfBookRemain
FROM 
    LMS.Books b
LEFT JOIN 
    LMS.Receipts r ON b.BookID = r.BookID AND r.GiveBack = 0
GROUP BY 
    b.BookID, b.NumOfCopy

方案二：子查询统计未归还数量 #

SELECT 
    BookID,
    (NumOfCopy - (
        SELECT COUNT(*) 
        FROM LMS.Receipts r 
        WHERE r.BookID = b.BookID AND r.GiveBack = 0
    )) AS NumberOfBookRemain
FROM 
    LMS.Books b

方案说明 #

• 左连接（LEFT JOIN）：确保所有图书都能被查询到，包括从未被借出的图书
• COALESCE函数：处理COUNT返回NULL的情况（无未归还记录时，COUNT为NULL，COALESCE将其转换为0，避免计算出现NULL值）
• 分组聚合：按图书ID和总馆藏数分组，统计每本图书的未归还记录总数
• 子查询方案：针对每本图书单独查询对应的未归还数量，逻辑直观易懂

内容的提问来源于stack exchange，提问作者Long Hoà