MySql 列出总和 > 0 的所有字段

Question

前段时间我有一个问题如何用多个count()写一个sql查询，and got needed answer here .

现在假设我有完全相同的表

ID|group_name|username
----------------------
1 |    A     | user1
----------------------
2 |    B     | user2
----------------------
3 |    C     | user1

...

和完全相同的查询:

select count(*) from TABLE 
where username='user1'
having (sum(group_name ='A') > 0
     and sum(group_name = 'B') > 0)
     or sum(group_name = 'C') > 0")

但现在我不想统计，而是列出所有与这个having语句对应的用户名。即列出所有同时在A 和B 或C 组中的用户名。

目前我只尝试将 count(*) 替换为 username 并在我的查询中添加 group by username ，像这样:

select username where username='user1' having (sum(group_name ='A') > 0 and sum(group_name = 'B') > 0) or sum(group_name = 'C') > 0") group by username

但是我得到语法错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY username LIMIT 0, 25' at line 1

非常感谢任何形式的帮助。

Answer 1

要列出满足您条件的用户名，您可以将查询写为

select username
from table
group by username
having (
    sum(group_name ='A') > 0 and sum(group_name = 'B') > 0
) or sum(group_name = 'C') > 0