SQL Server中计算图书总库存的SQL编写问题求助

数据表示例 #

Books表 #

BookNo BookName  BookType
123    ABC          1
555    XYZ          0

Shelf表 #

Shelf ShelfNo BookNo BookQuantity
  XB   XB01     123    5
  XB   XB02     555    3
  XB   XB03     123    8

BooksIssued表 #

ShelfNo BookName IssuedDate   QuantityIssued
  XB01    ABC      11/21/2022     2
  XB02    XYZ      11/20/2022     1
  XB03    ABC      11/21/2022     5

需求说明 #

计算图书总库存，结果按图书分组：

• 同一图书的所有Shelf.BookQuantity求和，加上该图书对应的BooksIssued.QuantityIssued总和，得到Total Stock
• BookType转换显示：1→adults，0→children
• 预期输出：

BookNo BookName BookType Total Stock
123     ABC       adults   20        -- (5+8+2+5)
555     XYZ      children   4         -- (3+1)

原SQL问题分析 #

当前SQL存在以下问题：

1. 分组维度错误：GROUP BY中包含s.shelfno、s.bookquantity、bi.issueddate，导致分组过细，无法按图书（BookNo）聚合数据
2. JOIN逻辑问题：直接关联Shelf和BooksIssued会导致数据重复，应该先分别聚合两个表的图书总量，再进行关联
3. 日期条件位置错误：WHERE中的日期过滤会排除掉没有发行记录的图书，应该将日期条件放到LEFT JOIN的ON子句中
4. 未处理BookType转换：没有将数字类型的BookType转换为指定文本
5. 未计算总库存：没有将库存总量和发行总量相加得到最终的Total Stock

修正后的SQL #

SELECT
    b.BookNo,
    b.BookName,
    CASE b.BookType
        WHEN 1 THEN 'adults'
        WHEN 0 THEN 'children'
    END AS BookType,
    COALESCE(s_total.total_shelf, 0) + COALESCE(bi_total.total_issued, 0) AS "Total Stock"
FROM Books b
LEFT JOIN (
    -- 按BookNo聚合Shelf表的库存总量
    SELECT BookNo, SUM(BookQuantity) AS total_shelf
    FROM Shelf
    WHERE Shelf = 'XB'
    GROUP BY BookNo
) s_total ON b.BookNo = s_total.BookNo
LEFT JOIN (
    -- 按BookName关联Books表，再按BookNo聚合发行总量（用BookNo关联更准确）
    SELECT b_inner.BookNo, SUM(bi.QuantityIssued) AS total_issued
    FROM BooksIssued bi
    JOIN Books b_inner ON bi.BookName = b_inner.BookName
    WHERE bi.IssuedDate BETWEEN '11/01/2022' AND '11/30/2022' -- 按需调整日期范围，示例数据发行日期在下旬，故扩大范围
    GROUP BY b_inner.BookNo
) bi_total ON b.BookNo = bi_total.BookNo
ORDER BY b.BookNo;

修正说明 #

1. 分步聚合：先分别对Shelf和BooksIssued表按图书维度聚合，避免直接关联导致的数据重复
2. 正确分组：仅按BookNo（图书唯一标识）进行分组，确保同一图书的所有数据被聚合
3. 日期条件调整：将发行日期的过滤放到子查询中，确保没有发行记录的图书也能被保留
4. BookType转换：使用CASE语句将数字类型转换为指定文本
5. 处理空值：用COALESCE函数处理可能的空值（比如某图书没有发行记录时，total_issued为0）

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