代码之家 › 专栏 › 技术社区 › Imanali Mamadiev Ghazaleh Javaheri

Mysql计数连接表OneToMany

left-join join sql mysql

2

Imanali Mamadiev Ghazaleh Javaheri · 技术社区 · 8 年前

任务:

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

3 回复 | 直到 6 年前

1

3

ScaisEdge 8 年前

在以下情况下,您可以使用基于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

2

1

Sas 8 年前

另一种方式

SELECT task.id, task.title, task.description,

SUM(DECODE (status, 'HOLD',1,0 end)) AS "HOLD_COUNT"
SUM(DECODE (status, OK,1, 0 end)) AS "OK_COUNT"
SUM(DECODE (status, ERROR,1, 0 end ) AS "ERROUR_COUNT"

FROM task
JOIN Message ON Task.id = Message.task_id
group by task.id, task.title  , task.description

3

1

Gordon Linoff 8 年前

我认为正确的解决方案是:

select t.id, t.title, t.description,
       coalesce(sum( m.status = 'Hold' ), 0) as num_hold,
       coalesce(sum( m.status = 'OK' ), 0) as num_ok,
       coalesce(sum( m.status = 'Error' ), 0) as num_error
from task t left join
     message m
     on m.task_id = t.id
group by t.id, t.title, t.description;