当前位置：首页 > article >正文

[sql-03] 求阅读至少两章的人数

article 2024/11/17 12:23:39

准备数据

CREATE TABLE `book_read` (
  `bookid` varchar(150) NOT NULL COMMENT '书籍ID',
  `username` varchar(150) DEFAULT NULL COMMENT '用户名',
  `seq` varchar(150) comment '章节ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户阅读表'


insert into book_read values('《太子日子》','刘晓','章节1');
insert into book_read values('《太子日子》','刘晓','章节2');
insert into book_read values('《太子日子》','刘晓','章节3');
insert into book_read values('《太子日子》','刘晓','章节3');

insert into book_read values('《太子日子》','张斌月','章节1');
insert into book_read values('《太子日子》','张斌月','章节2');

insert into book_read values('《太子日子》','彭*','章节1');
-- ---------------------------求每本书 阅读大于或等于两章的人数---------------------

sql


select 
	bookid, -- 书名
	count(distinct(case when rn>=2 then username else null end)) read_2 -- 阅读>=2章的
from (
	select 
		bookid,
		username,
		count(distinct (seq)) rn -- 阅读章节数量
	from book_read 
	
	group by bookid,username
) a
group by bookid