mysql where 变量,MySQL在where子句中使用用户定义的变量

sql query:

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

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

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.

解决方案

You can do it with group by and 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