如何从mysql查询中排除不常用的用户名

Question

我有这个代码

SELECT usernames FROM table1

我想从table1中选择每个username，但我想排除在table2中出现少于5次的usernames。

因此，我想要这样做：从表1中选择所有用户名，但排除表2中少于5行的用户名。

我该怎么做？

Answer 1

select table1.username 
from table1 
inner join (select username, 
count(*) as count from table2 
group by username 
having count > 5) as tmp 
on table1.username = tmp.username

Answer 2

select username from table1 where username in
(
select username from (
select  username ,
count( * )
from table2 
group by username
having count( * ) > 5
) as x
);

在mysql 5.6上测试：模式和代码