我有两个表(让我们称它们为案例和用户)结构如下: 案例
ID|status |user_id
__|____________|_______
1 |Open |6
2 |Closed |9
3 |Incomplete |9
用户:
ID|
__|_____
6|
9|
我想为 id 选择一个计数以及每个状态字段的计数。
目前我已经尝试过:
SELECT COUNT(c.id), COUNT(
(SELECT status from cases where status = 'Open')),
COUNT(
(SELECT status from cases where status = 'Closed')),
COUNT(
(SELECT status from cases where status = 'Incomplete'))
FROM cases as c
JOIN users as u on u.id = c.user_id
我收到返回多行的查询错误。
这就是我想要实现的目标
ID| Open | Closed | Incomplete |user_id
__|______|________|____________|__________
2|0 |1 |1 |9
回顾:每个用户 ID 的计数,用于计算 ID 的数量,以及每个状态的计数。
你可以试试这个sum
SELECT COUNT(c.id),
SUM(c.status = 'Open') Open ,
SUM(c.status = 'Closed') Closed ,
SUM(c.status = 'Incomplete') Incomplete,
u.id user_id
FROM cases as c
JOIN users as u on u.id = c.user_id
GROUP BY u.id