Mysql inq - 查询多个表的结果

Question

我正在编写一个问题跟踪脚本，并查看 mysql 语句，我能够获取所有优先问题的计数，但无法获取状态计数。

如何使用下面的 mysql 语句检索状态计数？

tbl_status
index    status
 1         open
 2         closed
 3         pending

tbl_priority
index      priority
1           low
2           medium
3             high


tbl_incident
incident  priority   status
adfadf        1          2
adfsdf        2          2
adfadf        1          1
adfadf        3          2
adfasdf       1          3

我能够将优先级分组(有效):

Low 3
Hedium 1
high 1

类似于状态的相同结果，但它不起作用。也许从一个声明中要求太多。

open 1
closed 3
high 1

try
{   $stmt = $dbcon1->query("SELECT COUNT(tbl_incident.status),
               tbl_priority.priority, count(tbl_incident.priority), tbl_status.status
          FROM tbl_incident

     LEFT JOIN tbl_priority 
            ON tbl_priority.index = tbl_incident.priority

     LEFT JOIN tbl_status 
            ON tbl_status.index = tbl_incident.status       


      GROUP BY tbl_priority.pry_priority ");    
        $priorityCount = $stmt->fetchAll(PDO::FETCH_ASSOC);       

}

Answer 1

maybe asking too much from a single statement.

是的，尝试将每个问题的陈述分开。我认为这会更有效率。

对于状态:

select tbl_status.status, count(*)
from tbl_incident 
    inner join tbl_status on tbl_status.index = tbl_incident.status
group by tbl_status.status;

优先级:

select tbl_priority.priority, count(*)
from tbl_incident 
    inner join tbl_priority on tbl_priority.index = tbl_incident.priority
group by tbl_priority.priority;