sql - MySQL:查找重复用户，其中项目计数< 1

Question

尝试通过电子邮件查找项目计数小于 0 的重复用户。我已经让重复用户正常工作(尽管它返回已排序的完整用户列表，而不是子集):

select users.id, email, count(email) as count
from users
group by users.email
order by count desc

我正在尝试加入 count(items.id) < 1 的项目，但这似乎不起作用。

select users.id, email, count(email) as count
from users
join items on items.user_id = users.id
having count(items.id) < 1
group by users.email
order by count desc

我也尝试过 IN 查询，但似乎语法不正确。

有什么简单的方法可以做到这一点吗？谢谢!

更新:

这个查询成功了:

    select DISTINCT(u1.id), u1.email
    from users u1
    inner join users u2
        on 1=1
        and u1.email = u2.email
        and u1.id != u2.id
    where not exists (select * from items i where i.user_id = u1.id)
    order by u1.id

Answer 1

重复的用户:

select 
    email, 
    count(*) as count,
    min(id) first_user_with_this_email_id
from users
group by email
having count(*) > 1

对于第二个，请尝试以下操作:

select 
    users.email, 
    count(*) as count
from users
left join items 
on (items.user_id = users.id)
where items.id is null --there are no related items
group by users.email
having count(*) > 1 --there are at least two users

第二个的另一个版本:

select 
    u.email, 
    count(*) as count
from users u
where not exists (select * from items i where i.user_id = u.id)
group by u.email
having count(*) > 1 --there are at least two users

确保您在 items 表中拥有 user_id 索引。