题解 | 统计借阅量

select
    t1.book_id,
    t1.book_title,
    sum(
        if(DATE_FORMAT(t3.borrow_date, '%Y-%m') = '2023-02',
        1,
        0)
    ) as feb_2023_borrows,
    sum(
        if(DATE_FORMAT(t3.borrow_date, '%Y-%m') = '2024-02',
        1,
        0)
    ) as feb_2024_borrows,
    sum(
        if(DATE_FORMAT(t3.borrow_date, '%Y-%m') = '2024-01',
        1,
        0)
    ) as jan_2024_borrows,
     sum(
        if(DATE_FORMAT(t3.borrow_date, '%Y-%m') = '2024-02',
        1,
        0)
    ) - sum(
        if(DATE_FORMAT(t3.borrow_date, '%Y-%m') = '2023-02',
        1,
        0)
    ) as yoy_delta,
    sum(
        if(DATE_FORMAT(t3.borrow_date, '%Y-%m') = '2024-02',
        1,
        0)
    ) - sum(
        if(DATE_FORMAT(t3.borrow_date, '%Y-%m') = '2024-01',
        1,
        0)
    ) as mom_delta,
    round(ifnull(sum(if(t2.region = '华北'and year(t3.borrow_date) = 2023,1,0)) / sum(if(year(t3.borrow_date) = 2023, 1, 0)) * 100,0),2) as north_pct_2023,
    round(ifnull(sum(if(t2.region = '华南'and year(t3.borrow_date) = 2023,1,0)) / sum(if(year(t3.borrow_date) = 2023, 1, 0)) * 100,0),2) as south_pct_2023,
    round(ifnull(sum(if(t2.region = '华东'and year(t3.borrow_date) = 2023,1,0)) / sum(if(year(t3.borrow_date) = 2023, 1, 0)) * 100,0),2) as east_pct_2023
from
    Books t1 
    left join BorrowRecords t3 on t1.book_id=t3.book_id
    left join Branches t2 on t2.branch_id=t3.branch_id
group by 
    t1.book_id,
    t1.book_title
order by book_id, book_title

1. DATE_FORMAT(time, '%Y-%m')可以取出年月份
2. 在同一层SELECT里不能用刚起的别名再做运算