我想找出满足特定条件但包括零的次数(每个经理手下有多少名特定名称的员工).
表:
| ID | name | manager_ID |
|---|---|---|
| 1 | John | 1 |
| 2 | John | 1 |
| 3 | Jane | 2 |
| 4 | John | 3 |
结果应该是:
| manager_ID | count |
|---|---|
| 1 | 2 |
| 2 | 0 |
| 3 | 1 |
使用WHERE不返回0值:
SELECT manager_id, COUNT(*)
FROM employees
WHERE name = 'John'
GROUP BY manager_id
我想找出满足特定条件但包括零的次数(每个经理手下有多少名特定名称的员工).
表:
| ID | name | manager_ID |
|---|---|---|
| 1 | John | 1 |
| 2 | John | 1 |
| 3 | Jane | 2 |
| 4 | John | 3 |
结果应该是:
| manager_ID | count |
|---|---|
| 1 | 2 |
| 2 | 0 |
| 3 | 1 |
使用WHERE不返回0值:
SELECT manager_id, COUNT(*)
FROM employees
WHERE name = 'John'
GROUP BY manager_id
买一百个吧.请使用CASE、expression到conditional aggregation.
SELECT manager_id,
sum(case when name = 'John' then 1 else 0 end)
FROM employees
GROUP BY manager_id;