php - 单表内连接mysql

Question

我已经创建了表格，其中日期将输入 3 种状态，已解决、未解决和搁置。我从中创建了以下查询我想创建条形图，它应该显示 3 个状态日期条。

select a.date, a.Solved,b.Unsolved, c.OnHold 
from (select count(prob_stat) as Solved, date from delivery 
where prob_stat='Solved' group by date) a 
inner join (select count(prob_stat) as Unsolved, date from delivery 
where prob_stat='Unsolved' group by date) b on a.date=b.date 
inner join (select count(prob_stat) as OnHold, date from delivery 
where prob_stat='OnHold' group by date) c on a.date=c.date

但它不起作用，结果我只能看到 1 条记录。有人可以帮忙吗？

普拉文

Answer 1

试试这个。

使用CASE 表达式。

查询

select `date`,
sum(case prob_stat when 'Solved' then 1 end) as Solved,
sum(case prob_stat when 'Unsolved' then 1 end) as Unsolved,
sum(case prob_stat when 'OnHold' then 1 end) as OnHold
from delivery
group by `date`;