修改SQL以统计相同biblionumber对应的所有题名借阅次数

我需要获取图书馆中按借阅次数排序的最受欢迎题名，之前使用的Koha SQL报表代码统计的是馆藏副本（items）的借阅次数，而非题名层面的总次数。我尝试基于biblionumber统计每个题名的总借阅次数，写出的SQL代码未达到预期效果：

SELECT
    biblio.title,
    biblio.author,
    biblio.biblionumber,
    COUNT(biblio.biblionumber) AS "count"
FROM statistics
LEFT JOIN items ON (statistics.itemnumber = items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
WHERE
    (statistics.datetime BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59')
    AND statistics.type = 'issue'
    AND items.itype IN ('BK', 'REF', 'TH', 'RES')
GROUP BY statistics.itemnumber, biblio.title, biblio.author 
ORDER BY COUNT(biblio.biblionumber) DESC LIMIT 10; 

预期效果是：比如biblionumber为83的题名应显示为1条记录，总借阅次数为11次。

修正后的SQL代码 #

SELECT
    biblio.title,
    biblio.author,
    biblio.biblionumber,
    COUNT(statistics.statistics_id) AS "借阅次数"
FROM statistics
JOIN items ON statistics.itemnumber = items.itemnumber
JOIN biblio ON items.biblionumber = biblio.biblionumber
WHERE
    statistics.datetime BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
    AND statistics.type = 'issue'
    AND items.itype IN ('BK', 'REF', 'TH', 'RES')
GROUP BY biblio.biblionumber, biblio.title, biblio.author
ORDER BY "借阅次数" DESC
LIMIT 10;

关键修正说明 #

• 分组逻辑调整：原代码按statistics.itemnumber分组，会导致同一个题名的不同副本被分开统计。改为按biblio.biblionumber（题名唯一标识）分组，确保同一个题名的所有借阅记录合并统计。
• 统计字段优化：用COUNT(statistics.statistics_id)统计借阅记录数，比统计biblio.biblionumber更准确，避免关联时可能出现的NULL值干扰。
• 关联类型调整：将LEFT JOIN改为JOIN，只统计有对应馆藏副本和题名的有效借阅记录，过滤掉无匹配项的无效统计条目。

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