mysql在where子句中使用用户定义的变量 [英] mysql using user-defined variable in where clause

问题描述

sql查询:

select
    u.username,
        @total_subscribers:=(
            select count(s.id)
                from subscribers as s
                where s.suid = u.uid
        ) as total_subscribers
from users as u
where @total_subscribers > 0

如果我删除了where @total_subscribers > 0，查询将显示所有用户及其总订阅者

if i remove where @total_subscribers > 0 query will show all users and their total subscribers

但是我只想显示那些拥有至少1个订阅者的用户...在我添加了where子句并使用定义的变量之后，我得到了一个空结果集.

but i want to show only those users who have at least 1 subscriber... after i add the where clause and use the defined variable i get an empty result set.

推荐答案

您可以使用group by和having:

select
   u.username,
   count(s.id) as total_subscribers
from users as u
inner join subscribers as s on s.suid = u.uid
group by u.id
having count(s.id) > 0

这篇关于mysql在where子句中使用用户定义的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！