DATA
数据
ID-USER-ROLE-PERM
1 1 1 1
2 1 1 2
3 1 1 3
4 1 1 4
5 1 1 5
6 1 1 6
12 1 1 7
13 1 1 8
9 1 3 1
10 1 3 5
11 1 4 1
14 13 1 8
Query description: How many users take value 1 as ROLE?
查询描述:有多少用户将值1作为角色?
Result should be: 2
结果应该是:2
because only user number 1 and 13 takes value 1 as ROLE
因为只有用户1和13以值1作为角色
This is my faulty query which returns 9:
这是我的错误查询，返回9:
SELECT COUNT(*) FROM assigned_roles WHERE ROLE = 1 GROUP BY ROLE
Any idea how to return 2 instead?
你知道怎么返回2吗?
1 个解决方案
#1
7
To count the number of different users use COUNT(DISTINCT USER).
要计算不同用户的数量，请使用count(不同的用户)。
Also you don't need GROUP BY ROLE as your WHERE clause ensures that there can only be a maximum of one group.
同样，您不需要按角色分组，因为WHERE子句确保最多只能有一个组。
SELECT COUNT(DISTINCT USER)
FROM assigned_roles
WHERE ROLE = 1
SQL小提琴
#1
7
To count the number of different users use COUNT(DISTINCT USER).
要计算不同用户的数量，请使用count(不同的用户)。
Also you don't need GROUP BY ROLE as your WHERE clause ensures that there can only be a maximum of one group.
同样，您不需要按角色分组，因为WHERE子句确保最多只能有一个组。
SELECT COUNT(DISTINCT USER)
FROM assigned_roles
WHERE ROLE = 1
SQL小提琴