mysql - 仅当一个字段不为 NULL 时才对 MySQL 条目进行分组，否则另一个字段带有 CASE

Question

我有一个 books 表，用于存储书籍版本。通常，同一本书会在我的数据库中多次插入，因为它以不同的形式存在(精装书、平装书、电子书等)。每本书都有以下字段(除其他外):

• id(唯一主键)
• 标题
• item(这是一个int，同一本书的两种不同形式将具有相同的item，这本书仅以一种形式存在将为 NULL)

现在，如果有另一个存储销售额的 sales 表，如下所示:

• id(唯一主键)
• book_id(与 book.id 相关)
• 日期(销售发生的日期)

我需要获取畅销书列表，将不同版本的书籍分组，就像同一本书一样，所以我的 sql 查询如下所示:

    SELECT `books`.`title`, COUNT(`sales`.`id`) 
    FROM `sales` 
    JOIN `books` ON `books`.`id` = `sales`.`book_id` 
    GROUP BY (CASE WHEN `item` IS NOT NULL THEN `item` ELSE `books`.`id` END)

问题在于，ID 为 27057 且项目为 NULL 的书籍《Margherita Dolcevita》与 id 为 49522 且项目为 27057 的书籍《Master of the Storm》分组。

我需要在我的 sql 查询(最好)或我的数据库方案中进行哪些更改才能仅通过一个查询获得我想要的结果？

Answer 1

问题出在 GROUP BY 子句中的 CASE 上。您将“item”与“id”混合在一起。

理想情况下，您应该在 books 表中为任何图书保留一个条目，并为具有以下字段的图书格式创建一个不同的表:

• id(PK，自动增量)；
• book_id(FK，书籍.id)；
• 格式(整数、字符串，任何适合您设计的格式)；

使用您当前的设计，尝试使您在 CASE 表达式中使用的两个 ID 的范围不重叠(例如，向 books 添加前缀。id):

GROUP BY (CASE WHEN `item` IS NOT NULL THEN `item` ELSE CONCAT('book-', `books`.`id`) END)

同一表达式的更简单形式(并且更易于阅读和理解)是:

GROUP BY IFNULL(`item`, CONCAT('book-', `books`.`id`))