SQL 查找用户的疑似小号

根据评论表remark(user_id, post_id, remark_time)，书写一个SQL语句，查找用户wang的疑似小号（疑似小号的定义如下：如果wang评论过的帖子，某用户都评论过，则认为该用户是wang的疑似小号）

判断wang 所评论的帖子，并且判断某用户是不是都有评论这些帖子。

假设wang的user_id=1

declare @totalNum int
begin
set @totalNum=(select count(1)totalNum from remark where user_id=1)
select user_id from(
    select user_id,count(1)userNum from remark 
    where post_id in (select post_id from remark where  user_id=1)
    group by user_id
)t
where userNum=@totalNum
end;

常用的SQL也能写，但是子语句嵌套可能会比较多

你是大块肥肉之家的吗

直接把所有帖子按顺序拼成一个字符串,再判断和wang的是否相等即可

select user_id from (
select user_id,group_concat(post_id ORDER BY post_id) g ,
max(case when user_id='wang' then group_concat(post_id ORDER BY post_id) end ) over() w
from remark group by user_id) t
where g=w and user_id<>'wang'

1、考虑小号可能评论过大号未评论过的帖子
2、考虑一个帖子一个账号可能进行多次评论

以下在MySQL8中可以正常运行（只建了表，没有做测试数据）

WITH t0 AS (
        SELECT DISTINCT post_id FROM remark WHERE user_id = 'wang' )
    , t1 AS (
        SELECT user_id, count( DISTINCT post_id ) cnt
          FROM remark a
         WHERE EXISTS( SELECT 1 FROM t0 b WHERE a.post_id = b.post_id ) AND a.user_id != 'wang'
         GROUP BY user_id )
SELECT user_id FROM t1 WHERE cnt = ( SELECT count(1) FROM t0 );

或者这样也是可以的：

WITH t0 AS (
        SELECT DISTINCT post_id FROM remark WHERE user_id = 'wang' )
    , t1 AS (
        SELECT user_id, count( DISTINCT post_id ) cnt
          FROM remark a
         WHERE EXISTS( SELECT 1 FROM t0 b WHERE a.post_id = b.post_id ) AND a.user_id != 'wang'
         GROUP BY user_id
         HAVING count( DISTINCT post_id ) = ( SELECT count(1) FROM t0 ) )
SELECT user_id FROM t1;