任务：
id | title | description |
---------------------------------------------------------------------
1 | Task1 | Descr1 |
2 | Task2 | Descr1 |
3 | Task2 | Descr1 |
4 | Task2 | Descr1 |
5 | Task2 | Descr1 |
消息：
id | task_id | message | status |
---------------------------------------------------------------------
1 | 1 | Message1 | HOLD
2 | 1 | Message2 | OK
3 | 1 | Message3 | ERROR
4 | 1 | Message4 | ERROR
5 | 2 | Message5 | HOLD
6 | 2 | Message6 | OK
7 | 2 | Message7 | OK
8 | 2 | Message7 | OK
9 | 3 | Message7 | OK
我想在这里展示：
id | title | description | count(HOLD) | count(OK) | count(ERROR)
---------------------------------------------------------------------
1 | Task1 | Descr1 | 1 | 1 | 2
2 | Task2 | Descr1 | 1 | 3 | 0
3 | Task2 | Descr1 | 0 | 1 | 0
4 | Task2 | Descr1 | 0 | 0 | 0
5 | Task2 | Descr1 | 0 | 0 | 0
最佳答案
当
select task.id
, task.title
, task.description
, sum(case when Message.status = 1 then 1 else 0 end ) status1
, sum(case when Message.status = 2 then 1 else 0 end ) status2
, sum(case when Message.status = 3 then 1 else 0 end ) status3
from Task
INNER JOIN Message ON Task.id = Message.task_id
group by task.id
, task.title
, task.description
关于mysql - Mysql计数连接表OneToMany，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49585618/