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

准备数据

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

执行结果