我从数据库中选择了这种表:
user project role
XXX p1 admin
XXX p2 developer
xxx p1 developer
yyy p3 tester
yyy p1 developer
yyy p1 admin
zzz p2 developer
我需要添加到此查询过滤器中:如果用户在一个项目中至少有两个角色。
我该怎么做?在这种情况下,哪些MySQL函数可以帮助我?
GROUP BY
项目并添加一个HAVING
子句以限制为具有COUNT
> 1
SELECT user, project, count(*) AS number_of_roles
FROM thetable
GROUP BY project
HAVING count(*) > 1
奖励:汇总所有角色的列
SELECT user, project, count(*) AS number_of_roles, GROUP_CONCAT(role) AS roles
FROM thetable
GROUP BY project
HAVING count(*) > 1