Mysql计数连接表OneToMany

[weixin_38071069]

任务： 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

[weixin_38071769]

你可以在sum和CASE上使用选择性聚合 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