首页 > 试题广场 >

统计借阅量

[编程题]统计借阅量
  • 热度指数：10663 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

为优化馆藏管理与读者服务，图书馆每月需复盘各图书的借阅表现。请基于借阅记录与分馆信息，统计每本书在 2024 年 2 月与 2024 年 1 月的借阅量，并与 2023 年 2 月对比；同时给出 2023 年各分区（华北、华东、华南）的借阅占比，用于评估区域结构变化。数据来自图书表、借阅表与分馆表。

【原始表】
Books（图书）表

  • book_id INT 主键：图书唯一标识
  • book_title VARCHAR：图书标题
  • category VARCHAR：图书所属类别

BorrowRecords（借阅记录）表

  • record_id INT 主键：借阅流水标识
  • book_id INT 外键 → Books.book_id
  • branch_id INT 外键 → Branches.branch_id
  • borrow_date DATE：借阅日期
  • member_id INT：借阅者ID（本题不直接使用）

Branches（分馆）表

  • branch_id INT 主键
  • branch_name VARCHAR：分馆名称
  • region ENUM('华北','华东','华南')：分区

输出每本书的借阅概览，结果需包含如下字段，并按 book_id 升序排列（若并列再按 book_title 升序）：

  • book_id：图书ID
  • book_title：图书名称
  • feb_2023_borrows：2023 年 2 月借阅量
  • feb_2024_borrows：2024 年 2 月借阅量
  • jan_2024_borrows：2024 年 1 月借阅量
  • yoy_delta：同比增量 = feb_2024_borrows - feb_2023_borrows
  • mom_delta：环比增量 = feb_2024_borrows - jan_2024_borrows
  • north_pct_2023：2023 年华北借阅占比（保留两位小数）
  • south_pct_2023：2023 年华南借阅占比（保留两位小数）
  • east_pct_2023：2023 年华东借阅占比（保留两位小数）

【示例输入】

Books

Branches

BorrowRecords

【示例输出】


示例1

输入

DROP TABLE IF EXISTS BorrowRecords;
DROP TABLE IF EXISTS Branches;
DROP TABLE IF EXISTS Books;

CREATE TABLE Books (
  book_id    INT PRIMARY KEY,
  book_title VARCHAR(100),
  category   VARCHAR(50)
);

CREATE TABLE Branches (
  branch_id   INT PRIMARY KEY,
  branch_name VARCHAR(50),
  region      ENUM('华北','华东','华南')
);

CREATE TABLE BorrowRecords (
  record_id   INT PRIMARY KEY,
  book_id     INT,
  branch_id   INT,
  borrow_date DATE,
  member_id   INT
);

INSERT INTO Books VALUES
(1,'Database Systems','IT'),
(2,'Linear Algebra','Math');

INSERT INTO Branches VALUES
(1,'北馆','华北'),
(2,'东馆','华东'),
(3,'南馆','华南');

INSERT INTO BorrowRecords VALUES
(101,1,1,'2023-02-10',11),
(102,1,2,'2024-02-12',12),
(103,1,3,'2024-01-20',13),
(104,2,1,'2023-02-15',21),
(105,2,2,'2023-07-01',22),
(106,2,3,'2024-02-03',23);

输出

book_id|book_title|feb_2023_borrows|feb_2024_borrows|jan_2024_borrows|yoy_delta|mom_delta|north_pct_2023|south_pct_2023|east_pct_2023
1|Database Systems|1|1|1|0|0|100.00|0.00|0.00
2|Linear Algebra|1|1|0|0|1|50.00|0.00|50.00

备注:
约束与说明：
  • 必须连接并使用三张表。
  • 必须使用高级查询（含：CTE/子查询、条件聚合、CASE/COALESCE，至少两类）。
  • 若某月份无借阅，按 0 计算；2023 年占比 = 2023 年对应分区借阅量 / 2023 年该书总借阅量 × 100，保留两位小数，若分母为 0 则各分区占比均为 0.00。
算法知识视频讲解
添加回答
漂亮的独行侠在思考头像 漂亮的独行侠在思考 
select b.book_id, book_title, 
sum(if(borrow_date like '2023-02%',1,0)) as feb_2023_borrows,
sum(if(borrow_date like '2024-02%',1,0)) as feb_2024_borrows,
sum(if(borrow_date like '2024-01%',1,0)) as jan_2024_borrows,
sum(if(borrow_date like '2024-02%',1,0)) - sum(if(borrow_date like '2023-02%',1,0)) as yoy_delta,
sum(if(borrow_date like '2024-02%',1,0)) - sum(if(borrow_date like '2024-01%',1,0)) as mom_delta,
round(ifnull(sum(if(region = '华北' and year(borrow_date) = '2023',1,0))/sum(if(year(borrow_date)=2023,1,0))*100,0),2) as north_pct_2023,
round(ifnull(sum(if(region = '华南' and year(borrow_date) = '2023',1,0))/sum(if(year(borrow_date)=2023,1,0))*100,0),2) as south_pct_2023,
round(ifnull(sum(if(region = '华东' and year(borrow_date) = '2023',1,0))/sum(if(year(borrow_date)=2023,1,0))*100,0),2) as east_pct_2023
from Books b left join BorrowRecords r on b.book_id = r.book_id
left join Branches c on r.branch_id = c.branch_id
group by b.book_id, book_title

发表于 2025-09-19 21:34:44 回复(0)
牛客128725104号头像 牛客128725104号
SELECT
a.book_id,
a.book_title,
IFNULL(a.feb_2023_borrows, 0) feb_2023_borrows,
IFNULL(a.feb_2024_borrows, 0) feb_2024_borrows,
IFNULL(a.jan_2024_borrows, 0) jan_2024_borrows,
IFNULL(a.feb_2024_borrows - a.feb_2023_borrows, 0) AS yoy_delta,
IFNULL(a.feb_2024_borrows - a.jan_2024_borrows, 0) AS mom_delta,
IFNULL(d.north_pct_2023, 0.00) north_pct_2023,
IFNULL(d.south_pct_2023, 0.00) south_pct_2023,
IFNULL(d.east_pct_2023, 0.00) east_pct_2023
FROM (
SELECT
b1.book_id,
b1.book_title,
SUM(IF(br.borrow_date BETWEEN '2023-02-01' AND '2023-02-28', 1, 0)) AS feb_2023_borrows,
SUM(IF(br.borrow_date BETWEEN '2024-01-01' AND '2024-01-31', 1, 0)) AS jan_2024_borrows,
SUM(IF(br.borrow_date BETWEEN '2024-02-01' AND '2024-02-29', 1, 0)) AS feb_2024_borrows
FROM Books b1
LEFT JOIN BorrowRecords br ON b1.book_id = br.book_id
GROUP BY b1.book_id, b1.book_title
) a
LEFT JOIN (
SELECT
c.book_id,
c.book_title,
ROUND(SUM(IF(YEAR(c.borrow_date) = 2023 AND c.region = '华北', 1, 0)) / c.total_number * 100, 2) AS north_pct_2023,
ROUND(SUM(IF(YEAR(c.borrow_date) = 2023 AND c.region = '华南', 1, 0)) / c.total_number * 100, 2) AS south_pct_2023,
ROUND(SUM(IF(YEAR(c.borrow_date) = 2023 AND c.region = '华东', 1, 0)) / c.total_number * 100, 2) AS east_pct_2023
FROM (
SELECT
b1.book_id,
b1.book_title,
b2.region,
br.borrow_date,
COUNT(*) OVER (PARTITION BY b1.book_id) AS total_number
FROM Books b1
LEFT JOIN BorrowRecords br ON b1.book_id = br.book_id
LEFT JOIN Branches b2 ON br.branch_id = b2.branch_id
WHERE YEAR(br.borrow_date) = 2023 OR br.borrow_date IS NULL
GROUP BY b1.book_id, b1.book_title, b2.region, br.borrow_date
) c
GROUP BY c.book_id, c.book_title
) d ON a.book_id = d.book_id
ORDER BY a.book_id, a.book_title;
发表于 2025-09-21 17:09:36 回复(0)
牛客342889858号头像 牛客342889858号 
with all_table as (
    select record_id,t1.book_id,t2.branch_id,borrow_date,member_id,book_title,category,branch_name,region, date_format(borrow_date,'%Y-%m')new_date
    from Books as t1
    left join BorrowRecords as t2 on t1.book_id=t2.book_id
    left join Branches as t3 on t3.branch_id=t2.branch_id
)
select book_id,book_title, sum(if(new_date='2023-02',1,0)) feb_2023_borrows ,
                ifnull(sum(if(new_date='2024-02',1,0)),0) feb_2024_borrows ,
                ifnull(sum(if(new_date='2024-01',1,0)),0) jan_2024_borrows,
                ifnull(sum(if(new_date='2024-02',1,0))-sum(if(new_date='2023-02',1,0)),0) yoy_delta,
                ifnull(sum(if(new_date='2024-02',1,0))- sum(if(new_date='2024-01',1,0)),0) mom_delta,
ifnull(round(sum(if(region='华北'and YEAR(borrow_date)=2023 ,1,0))/sum(if( YEAR(borrow_date)=2023,1,0))*100,2),0.00)north_pct_2023,
ifnull(round(sum(if(region='华南'and YEAR(borrow_date)=2023 ,1,0))/sum(if( YEAR(borrow_date)=2023,1,0))*100,2),0.00)
south_pct_2023,
ifnull(round(sum(if(region='华东'and YEAR(borrow_date)=2023 ,1,0))/sum(if( YEAR(borrow_date)=2023,1,0))*100,2),0.00)east_pct_2023
    from all_table
    group by book_id,book_title

发表于 2025-09-19 21:04:14 回复(0)
Mysql
已注销头像 已注销 
select
a.book_id,a.book_title,
ifnull(feb_2023_borrows,0) feb_2023_borrows,
ifnull(feb_2024_borrows,0) feb_2024_borrows,
ifnull(jan_2024_borrows,0) jan_2024_borrows,
ifnull(yoy_delta,0) yoy_delta,
ifnull(mom_delta,0) mom_delta,
ifnull(north_pct_2023,0.00) north_pct_2023,
ifnull(south_pct_2023,0.00) south_pct_2023,
ifnull(east_pct_2023,0.00) east_pct_2023
from Books a
left join
(
    select
    b.book_id,b.book_title,
    count(if(left(a.borrow_date,7)='2023-02',1,null)) feb_2023_borrows,
    count(if(left(a.borrow_date,7)='2024-02',1,null)) feb_2024_borrows,
    count(if(left(a.borrow_date,7)='2024-01',1,null)) jan_2024_borrows,
    count(if(left(a.borrow_date,7)='2024-02',1,null))-count(if(left(a.borrow_date,7)='2023-02',1,null)) yoy_delta,
    count(if(left(a.borrow_date,7)='2024-02',1,null))-count(if(left(a.borrow_date,7)='2024-01',1,null)) mom_delta,
    round(count(if(left(a.borrow_date,4)='2023' and c.region='华北',1,null))/count(if(left(a.borrow_date,4)='2023',1,null))*100,2) north_pct_2023,
    round(count(if(left(a.borrow_date,4)='2023' and c.region='华南',1,null))/count(if(left(a.borrow_date,4)='2023',1,null))*100,2) south_pct_2023,
    round(count(if(left(a.borrow_date,4)='2023' and c.region='华东',1,null))/count(if(left(a.borrow_date,4)='2023',1,null))*100,2) east_pct_2023
    from BorrowRecords a
    join Books b on a.book_id=b.book_id
    join Branches c on a.branch_id=c.branch_id
    group by b.book_id,b.book_title
) b
on a.book_id=b.book_id
order by book_id,book_title

发表于 2025-09-17 22:25:26 回复(0)
最喜欢冬天的沸羊羊很活泼头像 最喜欢冬天的沸羊羊很活泼
没有人吐槽一下吗
发表于 2026-01-15 17:08:52 回复(0)
非计算机专业入门菜鸡头像 非计算机专业入门菜鸡
字段名改改，结果格式改改就行了。我懒得改了。
CREATE TABLE if not exists  Books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    book_title VARCHAR(255) NOT NULL,
    category VARCHAR(50) NOT NULL
);
CREATE TABLE if not exists  Branches (
    branch_id INT PRIMARY KEY AUTO_INCREMENT,
    branch_name VARCHAR(100) NOT NULL,
    region VARCHAR(50) NOT NULL
);
CREATE TABLE if not exists BorrowRecords (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT NOT NULL,
    branch_id INT NOT NULL,
    borrow_date DATE NOT NULL,
    member_id INT NOT NULL,
    
    -- 外键约束（建议添加，保持数据一致性）
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (branch_id) REFERENCES Branches(branch_id)
);


/*

INSERT INTO Books (book_title, category) VALUES
('Database Systems', 'IT'),
('Linear Algebra', 'Math');
INSERT INTO Branches (branch_name, region) VALUES
('北馆', '华北'),
('东馆', '华东'),
('南馆', '华南');
INSERT INTO BorrowRecords (book_id, branch_id, borrow_date, member_id) VALUES
(1, 1, '2023-02-10', 11),
(1, 2, '2024-02-12', 12),
(1, 3, '2024-01-20', 13),
(2, 1, '2023-02-15', 21),
(2, 2, '2023-07-01', 22),
(2, 3, '2024-02-03', 23);
*/
drop table  if exists Books_hebing;
create table if not exists  Books_hebing
as select 
t1.book_id,
t1.book_title,
t2.branch_id ,
t2.borrow_date ,
t3.branch_name ,
t3.region 
from Books t1
left join BorrowRecords t2
on t1.book_id = t2.book_id 
left join Branches t3
on t2.branch_id = t3.branch_id;
SELECT * from Books_hebing;
drop table Books_hebing_tmp01;
create table if not exists Books_hebing_tmp01 as 
select 
t1.book_id,
t1.book_title,
t1.region,
max(t1.borrow_date) as borrow_date, 
sum(case when DATE_FORMAT(t1.borrow_date , '%Y-%m')= '2023-02' then 1 else 0 end )as feb_2023_borrows,
sum(case when DATE_FORMAT(t1.borrow_date , '%Y-%m')= '2024-02' then 1 else 0 end )as feb_2024_borrows,
sum(case when DATE_FORMAT(t1.borrow_date , '%Y-%m')= '2024-01' then 1 else 0 end )as jan_2024_borrows
from Books_hebing t1 
group by t1.book_id, t1.book_title,t1.region;
drop table Books_hebing_tmp02;

create table if not exists Books_hebing_tmp02 as select 
t1.*,
feb_2024_borrows - feb_2023_borrows  as yoy_delta,
feb_2024_borrows - jan_2024_borrows as mom_delta
from Books_hebing_tmp01 t1;
select * from Books_hebing_tmp02;

drop table if exists Books_hebing_tmp03;
create table if not exists Books_hebing_tmp03 as select 
t1.book_id,
t1.book_title,
sum(t1.feb_2023_borrows),
sum(t1.feb_2024_borrows),
sum(t1.jan_2024_borrows),
sum(t1.yoy_delta),
sum(t1.mom_delta),
sum(case when t1.region= '华北' and year(t1.borrow_date) = '2023' then 1 else 0 END)/sum(case when year(t1.borrow_date)= '2023'then 1 else 0 end) as north_pct_2023,
sum(case when t1.region= '华东' and year(t1.borrow_date) = '2023' then 1 else 0 end)/sum(case when year(t1.borrow_date)= '2023'then 1 else 0 end) as east_pct_2023,
sum(case when t1.region= '华南' and year(t1.borrow_date) = '2023' then 1 else 0 end)/sum(case when year(t1.borrow_date)= '2023'then 1 else 0 end) as south_pct_2023
from Books_hebing_tmp02 t1
group by t1.book_id, t1.book_title;


select * from Books_hebing_tmp03;

发表于 2026-01-13 17:22:09 回复(0)
在创作的饭团头像 在创作的饭团
WITH book_records AS (
SELECT
    b.book_id,
    b.book_title,
    SUM(CASE WHEN bo.borrow_date BETWEEN '2023-02-01' AND '2023-02-28' THEN 1 ELSE 0 END) AS feb_2023_borrows,
    SUM(CASE WHEN bo.borrow_date BETWEEN '2024-02-01' AND '2024-02-29' THEN 1 ELSE 0 END) AS feb_2024_borrows,
    SUM(CASE WHEN bo.borrow_date BETWEEN '2024-01-01' AND '2024-01-31' THEN 1 ELSE 0 END) AS jan_2024_borrows,
    ROUND( COALESCE(SUM(CASE WHEN br.region = '华北' AND YEAR(bo.borrow_date) = 2023 THEN 1 ELSE 0 END) / SUM(CASE WHEN YEAR(bo.borrow_date) = 2023 THEN 1 ELSE 0 END) * 100.0, 0)  ,2) AS 'north_pct_2023',
    ROUND( COALESCE(SUM(CASE WHEN br.region = '华南' AND YEAR(bo.borrow_date) = 2023 THEN 1 ELSE 0 END) / SUM(CASE WHEN YEAR(bo.borrow_date) = 2023 THEN 1 ELSE 0 END) * 100.0, 0)  ,2) AS 'south_pct_2023',
    ROUND( COALESCE(SUM(CASE WHEN br.region = '华东' AND YEAR(bo.borrow_date) = 2023 THEN 1 ELSE 0 END) / SUM(CASE WHEN YEAR(bo.borrow_date) = 2023 THEN 1 ELSE 0 END) * 100.0, 0)  ,2) AS 'east_pct_2023'
FROM
    Books AS b
LEFT JOIN
    BorrowRecords AS bo
ON b.book_id = bo.book_id
LEFT JOIN
    Branches AS br
ON br.branch_id = bo.branch_id
GROUP BY
    b.book_id,
    b.book_title
)

SELECT
    book_id,
    book_title,
    feb_2023_borrows,
    feb_2024_borrows,
    jan_2024_borrows,
    (feb_2024_borrows - feb_2023_borrows) AS yoy_delta,
    (feb_2024_borrows - jan_2024_borrows) AS mom_delta,
    north_pct_2023,
    south_pct_2023,
    east_pct_2023
FROM
    book_records
ORDER BY
    book_id,
    book_title;


发表于 2026-01-12 17:31:54 回复(0)
浩然正气123头像 浩然正气123 
with tab as( -- 整合总表
select bor.record_id,b.book_id,b.book_title,bra.region,bor.borrow_date
from Books as b
left join BorrowRecords as bor
on bor.book_id=b.book_id
left join Branches as bra
on bor.branch_id = bra.branch_id
)
-- 同比、环比增量、2023年各分区借阅占比统计
select book_id
,book_title,
feb_2023_borrows,
feb_2024_borrows,
jan_2024_borrows,
feb_2024_borrows-feb_2023_borrows as yoy_delta,
feb_2024_borrows-jan_2024_borrows as mom_delta,
if(total != 0,round(north_sum_2023/total*100,2),format(0,2)) as north_pct_2023,
if(total != 0,round(south_sum_2023/total*100,2),format(0,2)) as south_pct_2023,
if(total != 0,round(east_sum_2023/total*100,2),format(0,2)) as east_pct_2023
from(
-- 指定月借阅量、2023年分区借阅量、2023年借阅总量统计
select book_id,book_title,
count(case when borrow_date like '2023-02%' then 1 else null end) as feb_2023_borrows,
count(case when borrow_date like '2024-02%' then 1 else null end) as feb_2024_borrows,
count(case when borrow_date like '2024-01%' then 1 else null end) as jan_2024_borrows,
count(case when region ='华北' and year(borrow_date) = '2023' then 1 else null end) as north_sum_2023,
count(case when region ='华南' and year(borrow_date) = '2023' then 1 else null end) as south_sum_2023,
count(case when region ='华东' and year(borrow_date) = '2023' then 1 else null end) as east_sum_2023,
count(case when year(borrow_date) = '2023' then 1 else null end) as total
from tab
group by book_id,book_title
) as temp

发表于 2026-01-11 20:25:16 回复(0)
C#
sssdfsddf头像 sssdfsddf

with bor_log as (
    select
        A.book_id
        ,B.book_title 
        ,count(case when A.borrow_date>='2023-02-01' and A.borrow_date<'2023-03-01' then A.book_id end ) as feb_2023_borrows
        ,count(case when date_format(A.borrow_date,'%Y-%m') = '2024-02' then 1 end ) as feb_2024_borrows
        ,count(case when A.borrow_date>='2024-01-01' and A.borrow_date<'2024-02-01' then A.book_id end ) as jan_2024_borrows
        from BorrowRecords A,Books B 
        where A.borrow_date<'2024-03-01' and A.borrow_date>='2022-02-01' and  A.book_id=B.book_id
        group by 
        A.book_id
        ,B.book_title 
),
areas_borlog as (
    select 
         A.book_id
        ,B.book_title
        ,count(case when C.region='华北' then 1 end ) as north_cnt
        ,count(case when C.region='华南' then 1 end ) as south_cnt
        ,count(case when C.region='华东' then 1 end ) as east_cnt
        ,count(1) as total
        from BorrowRecords A,Branches C ,Books B
        where A.borrow_date<'2024-01-01' and A.borrow_date>='2023-01-01' and  A.branch_id=C.branch_id and A.book_id=B.book_id
        group by 
         A.book_id
        ,B.book_title
)

select 
     A.book_id
    ,A.book_title
    ,A.feb_2023_borrows
    ,A.feb_2024_borrows
    ,A.jan_2024_borrows
    ,(A.feb_2024_borrows - A.feb_2023_borrows) as yoy_delta
    ,(A.feb_2024_borrows - A.jan_2024_borrows) as mom_delta
    ,format(B.north_cnt*100/B.total,2) as north_pct_2023
    ,format(B.south_cnt/B.total *100,2) as south_pct_2023
    ,format(B.east_cnt/B.total *100,2) as east_pct_2023
    from bor_log A, areas_borlog B
    where A.book_id=B.book_id

发表于 2026-01-05 23:49:03 回复(0)
牛客189051092号头像 牛客189051092号
WITH t1 AS (
    SELECT 
        A.book_id,
        A.book_title,
        -- 2023年2月借阅量
        COALESCE(COUNT(CASE WHEN DATE_FORMAT(B.borrow_date, '%Y-%m') = '2023-02' THEN 1 END), 0) AS feb_2023_borrows,
        -- 2024年2月借阅量
        COALESCE(COUNT(CASE WHEN DATE_FORMAT(B.borrow_date, '%Y-%m') = '2024-02' THEN 1 END), 0) AS feb_2024_borrows,
        -- 2024年1月借阅量
        COALESCE(COUNT(CASE WHEN DATE_FORMAT(B.borrow_date, '%Y-%m') = '2024-01' THEN 1 END), 0) AS jan_2024_borrows
    FROM Books A
    LEFT JOIN BorrowRecords B ON A.book_id = B.book_id
    GROUP BY A.book_id, A.book_title
),
t2 AS (
    SELECT 
        B.book_id,
        -- 计算2023年2月的分区占比
        ROUND(COALESCE(SUM(CASE WHEN A.region = '华北' THEN 1 END) * 100.0 /
                NULLIF(COUNT(*), 0),0),2) AS north_pct_2023,
        ROUND(COALESCE(SUM(CASE WHEN A.region = '华南' THEN 1 END) * 100.0 /
                NULLIF(COUNT(*), 0),0),2) AS south_pct_2023,
        ROUND(COALESCE(SUM(CASE WHEN A.region = '华东' THEN 1 END) * 100.0 /
               NULLIF(COUNT(*), 0),0),2) AS east_pct_2023
    FROM BorrowRecords B
    JOIN Branches A ON B.branch_id = A.branch_id
    WHERE DATE_FORMAT(B.borrow_date, '%Y') = '2023'
    GROUP BY B.book_id
)
SELECT 
    t1.book_id,
    t1.book_title,
    t1.feb_2023_borrows,
    t1.feb_2024_borrows,
    t1.jan_2024_borrows,
    -- 同比增量
    (t1.feb_2024_borrows - t1.feb_2023_borrows) AS yoy_delta,
    -- 环比增量
    (t1.feb_2024_borrows - t1.jan_2024_borrows) AS mom_delta,
    -- 2023年分区占比
    COALESCE(t2.north_pct_2023, 0) AS north_pct_2023,
    COALESCE(t2.south_pct_2023, 0) AS south_pct_2023,
    COALESCE(t2.east_pct_2023, 0) AS east_pct_2023
FROM t1
LEFT JOIN t2 ON t1.book_id = t2.book_id
ORDER BY t1.book_id, t1.book_title;
发表于 2026-01-02 14:13:18 回复(0)
牛客972538838号头像 牛客972538838号 
select 
    book_id,
    book_title,
    feb_2023_borrows,
    feb_2024_borrows,
    jan_2024_borrows,
    feb_2024_borrows - feb_2023_borrows as yoy_delta,
    feb_2024_borrows - jan_2024_borrows as mom_delta,
    round(coalesce(north_borrows * 100 / nullif(total_2023_borrows, 0), 0), 2) as north_pct_2023,
    round(coalesce(south_borrows * 100 / nullif(total_2023_borrows, 0), 0), 2) as south_pct_2023,
    round(coalesce(east_borrows * 100 / nullif(total_2023_borrows, 0), 0), 2) as east_pct_2023
from (
    select
        book_id,
        book_title,
        sum(if(borrow_date between '2023-02-01' and last_day('2023-02-01'), 1, 0)) as feb_2023_borrows,
        sum(if(borrow_date between '2024-02-01' and last_day('2024-02-01'), 1, 0)) as feb_2024_borrows,
        sum(if(borrow_date between '2024-01-01' and last_day('2024-01-01'), 1, 0)) as jan_2024_borrows,
        sum(if(borrow_date between '2023-01-01' and '2023-12-31' and region = '华北', 1, 0)) as north_borrows,
        sum(if(borrow_date between '2023-01-01' and '2023-12-31' and region = '华南', 1, 0)) as south_borrows,
        sum(if(borrow_date between '2023-01-01' and '2023-12-31' and region = '华东', 1, 0)) as east_borrows,
        sum(if(borrow_date between '2023-01-01' and '2023-12-31', 1, 0)) as total_2023_borrows
    from Books
    left join BorrowRecords using(book_id)
    left join Branches using(branch_id)
    group by book_id, book_title
) t

发表于 2026-01-01 15:58:30 回复(0)
牛客599550900号头像 牛客599550900号
select
a.book_id,book_title,
sum(case when borrow_date like '2023-02%' then 1 else 0 end) as feb_2023_borrows,
sum(case when borrow_date like '2024-02%' then 1 else 0 end) as feb_2024_borrows,
sum(case when borrow_date like '2024-01%' then 1 else 0 end) as jan_2024_borrows,
sum(case when borrow_date like '2024-02%' then 1 else 0 end) -sum(case when borrow_date like '2023-02%' then 1 else 0 end) as yoy_delta,
sum(case when borrow_date like '2024-02%' then 1 else 0 end)-sum(case when borrow_date like '2024-01%' then 1 else 0 end) as mom_delta,
round(ifnull(sum(case when borrow_date like '2023%' and region like'%华北%' then 1 else 0 end)/sum(case when borrow_date like '2023%' then 1 else 0 end),0)*100.00, 2) as north_pct_2023,
round(ifnull(sum(case when borrow_date like '2023%' and region like'%华南%' then 1 else 0 end)/sum(case when borrow_date like '2023%' then 1 else 0 end),0)*100.00, 2) as south_pct_2023,
round(ifnull(sum(case when borrow_date like '2023%' and region like'%华东%' then 1 else 0 end)/sum(case when borrow_date like '2023%' then 1 else 0 end),0)*100.00, 2) as east_pct_2023
from
Books a
left join BorrowRecords c on a.book_id=c.book_id
left join Branches b on b.branch_id=c.branch_id
group by a.book_id,book_title
发表于 2025-12-26 11:34:12 回复(0)
有胆量的托尼在迎接offer头像 有胆量的托尼在迎接offer
用例中有连接后出现空值为0的情况，遂只能用 left join 加上 ifnull 语句处理 
select book_id,
    book_title,
    ifnull(sum(date_format(borrow_date,"%Y-%m") = "2023-02"),0) as feb_2023_borrows,
    ifnull(sum(date_format(borrow_date,"%Y-%m") = "2024-02"),0) as feb_2024_borrows,
    ifnull(sum(date_format(borrow_date,"%Y-%m") = "2024-01"),0) as jan_2024_borrows,
    ifnull(sum(date_format(borrow_date,"%Y-%m") = "2024-02") - sum(date_format(borrow_date,"%Y-%m") = "2023-02"),0) as yoy_delta,
    ifnull(sum(date_format(borrow_date,"%Y-%m") = "2024-02") - sum(date_format(borrow_date,"%Y-%m") = "2024-01"),0) as mom_delta,
    ifnull(round(sum(region = "华北" and date_format(borrow_date,"%Y") = "2023") / sum(date_format(borrow_date,"%Y") = "2023") * 100, 2),0) as north_pct_2023,
    ifnull(round(sum(region = "华南" and date_format(borrow_date,"%Y") = "2023") / sum(date_format(borrow_date,"%Y") = "2023") * 100, 2),0) as south_pct_2023,
    ifnull(round(sum(region = "华东" and date_format(borrow_date,"%Y") = "2023") / sum(date_format(borrow_date,"%Y") = "2023") * 100, 2),0) as east_pct_2023
from Books
    left join BorrowRecords using(book_id)
    left join Branches using(branch_id)
group by book_id,book_title


发表于 2025-12-19 15:50:12 回复(0)
牛客461283432号头像 牛客461283432号 
with t1 as(
    select
        book_id,
        count(case when date_format(borrow_date,'%Y-%m') = '2023-02' then record_id end) as feb_2023_borrows,
        count(case when date_format(borrow_date,'%Y-%m') = '2024-02' then record_id end) as feb_2024_borrows,
        count(case when date_format(borrow_date,'%Y-%m') = '2024-01' then record_id end) as jan_2024_borrows
    from BorrowRecords
    where date_format(borrow_date,'%Y-%m') in ('2023-02','2024-02','2024-01')
    group by book_id
)
, t2 as(
    select
        a.book_id,
        count(a.record_id) as total,
        count(case when b.region = '华北' then a.record_id end) as north_2023,
        count(case when b.region = '华南' then a.record_id end) as south_2023,
        count(case when b.region = '华东' then a.record_id end) as east_2023
    from BorrowRecords a left join Branches b on a.branch_id = b.branch_id
    where year(a.borrow_date) = '2023'
    group by book_id
)
select
    b.book_id,
    b.book_title,
    coalesce(t1.feb_2023_borrows,0) as feb_2023_borrows,
    coalesce(t1.feb_2024_borrows,0) as feb_2024_borrows,
    coalesce(t1.jan_2024_borrows,0) as jan_2024_borrows,
    coalesce((t1.feb_2024_borrows - t1.feb_2023_borrows),0) as yoy_delta,
    coalesce((t1.feb_2024_borrows - t1.jan_2024_borrows),0) as mom_delta,
    round(coalesce((t2.north_2023 / t2.total), 0)*100, 2) as north_pct_2023,
    round(coalesce((t2.south_2023 / t2.total), 0)*100, 2) as south_pct_2023,
    round(coalesce((t2.east_2023 / t2.total), 0)*100, 2) as east_pct_2023
from Books b left join t1 on b.book_id = t1.book_id left join t2 on b.book_id = t2.book_id
order by b.book_id, b.book_title asc;


发表于 2025-12-11 14:27:32 回复(0)
牛客838436385号头像 牛客838436385号 
with t1 as (
    select
        Books.book_id,
        book_title,
        sum(if(borrow_date between '2023-02-01' and '2023-02-28', 1, 0)) as feb_2023_borrows,
        sum(if(borrow_date between '2024-02-01' and '2024-02-29', 1, 0)) as feb_2024_borrows,
        sum(if(borrow_date between '2024-01-01' and '2024-01-31', 1, 0)) as jan_2024_borrows,
        sum(if(year(borrow_date) = 2023, 1, 0)) as total_2023,
        sum(if(region='华北' and year(borrow_date) = 2023, 1, 0)) as north_2023,
        sum(if(region='华南' and year(borrow_date) = 2023, 1, 0)) as south_2023,
        sum(if(region='华东' and year(borrow_date) = 2023, 1, 0)) as east_2023
    from Books 
    left join BorrowRecords on Books.book_id = BorrowRecords.book_id
    left join Branches on BorrowRecords.branch_id = Branches.branch_id
    group by book_id, book_title
)
select
    book_id,
    book_title,
    feb_2023_borrows,
    feb_2024_borrows,
    jan_2024_borrows,
    feb_2024_borrows - feb_2023_borrows as yoy_delta,
    feb_2024_borrows - jan_2024_borrows as mom_delta,
    round(ifnull(north_2023 / total_2023 * 100, 0), 2) as north_pct_2023,
    round(ifnull(south_2023 / total_2023 * 100, 0), 2) as south_pct_2023,
    round(ifnull(east_2023 / total_2023 * 100, 0), 2) as east_pct_2023
from t1

发表于 2025-12-09 12:09:21 回复(0)
牛客418357468号头像 牛客418357468号 
SELECT
    b.book_id,
    b.book_title,
    -- 2023年2月借阅量
    COUNT(CASE WHEN br.borrow_date >= '2023-02-01' AND br.borrow_date < '2023-03-01' THEN 1 END) AS feb_2023_borrows,
    -- 2024年2月借阅量
    COUNT(CASE WHEN br.borrow_date >= '2024-02-01' AND br.borrow_date < '2024-03-01' THEN 1 END) AS feb_2024_borrows,
    -- 2024年1月借阅量
    COUNT(CASE WHEN br.borrow_date >= '2024-01-01' AND br.borrow_date < '2024-02-01' THEN 1 END) AS jan_2024_borrows,
    -- 同比增量
    (COUNT(CASE WHEN br.borrow_date >= '2024-02-01' AND br.borrow_date < '2024-03-01' THEN 1 END) 
     - COUNT(CASE WHEN br.borrow_date >= '2023-02-01' AND br.borrow_date < '2023-03-01' THEN 1 END)) AS yoy_delta,
    -- 环比增量
    (COUNT(CASE WHEN br.borrow_date >= '2024-02-01' AND br.borrow_date < '2024-03-01' THEN 1 END) 
     - COUNT(CASE WHEN br.borrow_date >= '2024-01-01' AND br.borrow_date < '2024-02-01' THEN 1 END)) AS mom_delta,
    -- 2023年华北占比（保留两位小数）
    ROUND(
        (COUNT(CASE WHEN YEAR(br.borrow_date)=2023 AND bra.region='华北' THEN 1 END) 
         / NULLIF(COUNT(CASE WHEN YEAR(br.borrow_date)=2023 THEN 1 END), 0)) * 100,
        2
    ) AS north_pct_2023,
    -- 2023年华南占比
    ROUND(
        (COUNT(CASE WHEN YEAR(br.borrow_date)=2023 AND bra.region='华南' THEN 1 END) 
         / NULLIF(COUNT(CASE WHEN YEAR(br.borrow_date)=2023 THEN 1 END), 0)) * 100,
        2
    ) AS south_pct_2023,
    -- 2023年华东占比
    ROUND(
        (COUNT(CASE WHEN YEAR(br.borrow_date)=2023 AND bra.region='华东' THEN 1 END) 
         / NULLIF(COUNT(CASE WHEN YEAR(br.borrow_date)=2023 THEN 1 END), 0)) * 100,
        2
    ) AS east_pct_2023
FROM Books b
LEFT JOIN BorrowRecords br ON b.book_id = br.book_id
LEFT JOIN Branches bra ON br.branch_id = bra.branch_id
-- 按图书ID、名称分组
GROUP BY b.book_id, b.book_title
-- 按要求排序
ORDER BY b.book_id ASC, b.book_title ASC;

发表于 2025-12-04 10:02:41 回复(0)
Mysql
MiserT头像 MiserT 
with
    monthly_borrow_info as (
        #1.按照书本id 计算2023-02借阅量/2024-02借阅量/2024-01借阅量
        #2.计算 月同期增量/月环增量
        select
            book_id,
            # 计算2023-02借阅量/2024-02借阅量/2024-01借阅量
            sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2023-02' then 1
                    else 0
                end
            ) feb_2023_borrows,
            sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2024-02' then 1
                    else 0
                end
            ) feb_2024_borrows,
            sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2024-01' then 1
                    else 0
                end
            ) jan_2024_borrows,
            # 计算月同期增量
            sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2024-02' then 1
                    else 0
                end
            ) - sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2023-02' then 1
                    else 0
                end
            ) yoy_delta,
            # 计算月环期增量
            sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2024-02' then 1
                    else 0
                end
            ) - sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2024-01' then 1
                    else 0
                end
            ) mom_delta
        from
            BorrowRecords
        group by
            book_id
    ),
    region_borrow_pct as (
        # 计算2023年每本书在华北/华南/华东的借阅量比例
        # 该书在本区域的借阅量/该书总借阅量
        select
            book_id,
            # 
            round(
                coalesce(
                    sum(
                        case
                            when region = '华北' then 1
                            else 0 
                            end
                        ) / count(*)*100
                        ,0
                    ),
                    2
            ) north_pct_2023,
            round(
                coalesce(
                    sum(
                        case
                            when region = '华南' then 1
                            else 0
                            end
                        ) / count(*)*100,
                        0
                    ),
                    2
            ) south_pct_2023,
            round(
                coalesce(
                    sum(
                        case
                            when region = '华东' then 1
                            else 0
                            end
                        ) / count(*)*100,
                        0
                    ),
                    2
            ) east_pct_2023
        from
            BorrowRecords
            join Branches using (branch_id)
        where
            #2023年
            year(borrow_date) = '2023'
        group by
            book_id
    )

# 合并
# 左连接Books表，没有借阅过的数据填0
    select 
        b.book_id,
        book_title,
        coalesce(feb_2023_borrows,0) feb_2023_borrows,
        coalesce(feb_2024_borrows,0) feb_2024_borrows,
        coalesce(jan_2024_borrows,0) jan_2024_borrows,

        coalesce(yoy_delta,0) yoy_delta,
        coalesce(mom_delta,0) mom_delta,

        coalesce(north_pct_2023,0) north_pct_2023,
        coalesce(south_pct_2023,0) south_pct_2023,
        coalesce(east_pct_2023,0) east_pct_2023
    from
        Books b
        left join monthly_borrow_info using(book_id)
        left join region_borrow_pct using(book_id)
    order by 
        b.book_id,
        book_title
用了两种方法写了，感觉还是很啰嗦，哈哈哈哈哈
发表于 2025-12-03 16:59:43 回复(0)
Tendernessnick头像 Tendernessnick 
# 各个图书在特定时间的借阅量，和指定时间对比，同时给出各分区占比
with
t1 as(
    select
        book_id,
        book_title,
        count(if(date_format(borrow_date,'%Y%m')='202302',1,null)) as feb_2023_borrows,
        count(if(date_format(borrow_date,'%Y%m')='202402',1,null)) as feb_2024_borrows,
        count(if(date_format(borrow_date,'%Y%m')='202401',1,null)) as jan_2024_borrows,
        count(if(date_format(borrow_date,'%Y')='2023' && branch_id=1,1,null)) as north_pct_2023_cnt,
        count(if(date_format(borrow_date,'%Y')='2023' && branch_id=3,3,null)) as south_pct_2023_cnt,
        count(if(date_format(borrow_date,'%Y')='2023' && branch_id=2,2,null)) as east_pct_2023_cnt,
        count(if(date_format(borrow_date,'%Y')='2023',1,null)) as total_pct_2023_cnt
    from
        BorrowRecords
        right join Books using(book_id)
        left join Branches using(branch_id)
    group by
        book_id,
        book_title
)
,
t2 as(
    select
        book_id,
        book_title,
        feb_2023_borrows,
        feb_2024_borrows,
        jan_2024_borrows,
        feb_2024_borrows-feb_2023_borrows as yoy_delta,
        feb_2024_borrows-jan_2024_borrows as mom_delta,
        (
            case
                when total_pct_2023_cnt=0 then 0.00
                else round(north_pct_2023_cnt/total_pct_2023_cnt*100,2)
            end
        ) as north_pct_2023,
        (
            case
                when total_pct_2023_cnt=0 then 0.00
                else round(south_pct_2023_cnt/total_pct_2023_cnt*100,2)
            end
        ) as south_pct_2023,
        (
            case
                when total_pct_2023_cnt=0 then 0.00
                else round(east_pct_2023_cnt/total_pct_2023_cnt*100,2)
            end
        ) as east_pct_2023
    from
        t1
    order by
        book_id,
        book_title
)

select * from t2


发表于 2025-12-01 15:40:31 回复(0)
牛客760806908号头像 牛客760806908号
with t0 as (
    select
        b.book_id,
        b.book_title,
        region,
        substr(borrow_date,1,7) as mon,
        count(record_id) as record_cnt
    from  Books b
    left join
    (
        select
            *
        from BorrowRecords
        where borrow_date between '2023-01-01' and '2024-02-28'
    )a
    on a.book_id =b.book_id
    left join Branches c
    on a.branch_id=c.branch_id
    group by 1,2,3,4
)
,t1 as(
    select
        book_id,
        region,
        round(sum_cnt_region/sum(sum_cnt_region) over(partition by book_id)*100,2) as per_region
    from (
        select
            book_id,
            region,
            sum(record_cnt) as sum_cnt_region
        from t0
        where substr(mon,1,4) = '2023'
        group by 1,2
    )d
)
select
    h.book_id,
    book_title,
    sum(case when tag = 3 then record_cnt else 0 end) as feb_2023_borrows,
    sum(case when tag = 1 then record_cnt else 0 end) as feb_2024_borrows,
    sum(case when tag = 2 then record_cnt else 0 end) as jan_2024_borrows,
    sum(case when tag = 1 then record_cnt else 0 end)-sum(case when tag = 3 then record_cnt else 0 end) as yoy_delta,
    sum(case when tag = 1 then record_cnt else 0 end)-sum(case when tag = 2 then record_cnt else 0 end) as mom_delta,
    case when north_pct_2023 is not null then north_pct_2023 else 0 end as north_pct_2023,
    case when south_pct_2023 is not null then south_pct_2023 else 0 end as south_pct_2023,
    case when east_pct_2023 is not null then east_pct_2023 else 0 end as east_pct_2023
from (
    select
        book_id,
        book_title,
        case when mon = '2024-02' then 1 when mon = '2024-01' then 2 when mon = '2023-02' then 3 end as tag,
        sum(record_cnt) as record_cnt
    from t0
    group by 1,2,3
)h
left join (
    select
        book_id,
        max(north_pct_2023)north_pct_2023,
        max(south_pct_2023)south_pct_2023,
        max(east_pct_2023)east_pct_2023
    from (
    select
        book_id,
        case when region='华北' then per_region else 0 end as north_pct_2023,
        case when  region='华南' then per_region else 0 end as south_pct_2023,
        case when  region='华东' then per_region else 0 end as east_pct_2023
    from t1
    ) t2
    group by 1
)g
on h.book_id = g.book_id
group by  1,2,8,9,10
order by book_id asc,book_title asc











发表于 2025-11-21 17:36:28 回复(0)
牛客115568740号头像 牛客115568740号
select b.book_id,
book_title,
ifnull(feb_2023_borrows,0) feb_2023_borrows,
ifnull(feb_2024_borrows,0) feb_2024_borrows,
ifnull(jan_2024_borrows,0) jan_2024_borrows,
ifnull(feb_2024_borrows,0) - ifnull(feb_2023_borrows,0) yoy_delta,
ifnull(feb_2024_borrows,0) - ifnull(jan_2024_borrows,0) mom_delta,
ifnull(north_pct_2023,0.00) north_pct_2023,
ifnull(south_pct_2023,0.00) south_pct_2023,
ifnull(east_pct_2023,0.00) east_pct_2023
from Books b
left join
(select book_id,
count(*) feb_2023_borrows
from BorrowRecords
where year(borrow_date)='2023' and month(borrow_date)='2'
group by book_id) n1
on b.book_id=n1.book_id
left join (select book_id,
count(*) feb_2024_borrows
from BorrowRecords
where year(borrow_date)='2024' and month(borrow_date)='2'
group by book_id) n2
on b.book_id=n2.book_id
left join (select book_id,
count(*) jan_2024_borrows
from BorrowRecords
where year(borrow_date)='2024' and month(borrow_date)='1'
group by book_id) n3
on b.book_id=n3.book_id
left join (select book_id,
round(sum(case when region='华北' then 1 else 0 end)/count(b.branch_id)*100,2) north_pct_2023,
round(sum(case when region='华南' then 1 else 0 end)/count(b.branch_id)*100,2) south_pct_2023,
round(sum(case when region='华东' then 1 else 0 end)/count(b.branch_id)*100,2) east_pct_2023
from BorrowRecords b
join Branches br
on b.branch_id=br.branch_id
where year(borrow_date)='2023'
group by book_id) n4
on b.book_id=n4.book_id

一直忽略了none值的存在，一定要记得把none值改为0 啊啊啊
发表于 2025-11-16 21:38:26 回复(0)
提交观点

问题信息

难度：
31条回答 135浏览

热门推荐

相关试题

统计借阅量

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号