准备数据
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
from (select bookid,username,count(distinct (seq)) rn from book_read group by bookid,username
) a
group by bookid
执行结果
