查询当月借阅量最高资源的SQL实现问题求助

你的现有代码没有过滤当前月份的借阅记录，也没有筛选出借阅次数最多的资源。以下是两种可行的解决方案，适用于大多数主流数据库（如MySQL、PostgreSQL、SQL Server）：

方案一：使用窗口函数（推荐，支持并列第一） #

这种方式用RANK()窗口函数直接对资源的借阅次数排名，筛选出排名第一的资源，同时支持多个资源并列最多借阅次数的场景：

SELECT ResourceId, Name
FROM (
    SELECT 
        r.ResourceId,
        r.Name,
        COUNT(l.LoanId) AS borrow_count,
        -- 按借阅次数降序排名，次数相同的资源排名一致
        RANK() OVER (ORDER BY COUNT(l.LoanId) DESC) AS rank_num
    FROM Resources r
    LEFT JOIN Loan l 
        ON r.ResourceId = l.ResourceId
        -- 过滤出当前月份的借阅记录
        AND YEAR(l.DateBorrowed) = YEAR(CURDATE())
        AND MONTH(l.DateBorrowed) = MONTH(CURDATE())
    GROUP BY r.ResourceId, r.Name
) AS ranked_resources
WHERE rank_num = 1
-- 若需要排除从未被借阅的资源，取消下面注释
-- AND borrow_count > 0

关键逻辑说明： #

1. 当前月份过滤：通过YEAR(DateBorrowed)和MONTH(DateBorrowed)匹配当前系统日期的年月，确保只统计本月的借阅记录
2. 分组统计：按资源ID和名称分组，计算每个资源的本月借阅次数
3. 排名筛选：用RANK()函数给资源按借阅次数降序排名，外层查询取排名为1的记录，就是本月借阅次数最多的资源

方案二：分步统计（兼容旧版本数据库） #

如果你的数据库不支持窗口函数，可以用分步统计的方式，先算出本月各资源的借阅次数，再找到最大次数，最后关联获取资源信息：

-- 统计本月各资源的借阅次数
WITH monthly_borrows AS (
    SELECT 
        ResourceId,
        COUNT(LoanId) AS borrow_count
    FROM Loan
    WHERE YEAR(DateBorrowed) = YEAR(CURDATE())
        AND MONTH(DateBorrowed) = MONTH(CURDATE())
    GROUP BY ResourceId
),
-- 找出本月最大借阅次数
max_borrow_count AS (
    SELECT MAX(borrow_count) AS max_count
    FROM monthly_borrows
)
-- 关联资源表获取最终结果
SELECT 
    r.ResourceId,
    r.Name
FROM Resources r
JOIN monthly_borrows mb ON r.ResourceId = mb.ResourceId
JOIN max_borrow_count mbc ON mb.borrow_count = mbc.max_count

关键逻辑说明： #

1. 第一个CTEmonthly_borrows单独统计本月每个资源的借阅次数
2. 第二个CTEmax_borrow_count计算出本月的最大借阅次数
3. 最后通过两次关联，筛选出借阅次数等于最大次数的资源

针对你现有代码的改进点 #

你的原始代码需要补充两个核心逻辑：

• 增加当前月份的过滤条件，避免统计所有历史借阅数据
• 加入最大借阅次数的筛选逻辑，而不是返回所有资源的借阅次数

以你的测试数据为例，如果当前系统月份是2022-10，R905有2次借阅记录，会成为查询结果的唯一（或并列）资源。

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