任务:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句