按输出分隔的值分组

Question

我有一个这样类型的数据：

| company_id | role_id |  
| 1          | 1       |
| 2          | 2       |
| 1          | 2       |

这是我的查询，它选择一个公司id并作为参数角色id传递。

SELECT company_id AS companyId, COUNT(role_id) AS usersNumber 
FROM companies
WHERE role_id = :userId
GROUP BY companyId

其中:userId是从JPA传递的参数。

它产生了这样的东西：

| company_id | passed_role_id_count |
| 1          | 1                    |
| 2          | 1                    |

我想要实现的是这样的输出：

| company_id | first_role_count | second_role_count |
| 1          | 1                | 1                 |
| 2          | 0                | 1                 |

有可能有这样的输出吗？或者，从DB中选择所有内容，然后使用Java Streams进行分组，这样更好吗？

Answer 1

使用过滤聚合：

SELECT company_id AS companyId, 
COUNT(*) filter (where role_id = 1) AS first_role_count, 
COUNT(*) filter (where role_id = 2) AS second_role_count
FROM companies
WHERE role_id = :userId
GROUP BY companyId

Answer 2

在@jarlh的提示下，我设法解决了我的问题：

SELECT eu.company_id,
SUM(CASE WHEN eu.role_id = 2 THEN 1 ELSE 0 END) AS "first_role",
SUM(CASE WHEN eu.role_id = 3 THEN 1 ELSE 0 END) AS "second_role"
FROM users eu
GROUP BY eu.company_id;