内部在单个表中加入mysql

I have created table where on date there will be 3 status entered, Solved, Unsolved and OnHold. I have created following query from this I want to create bar chart which should show 3 bars of status date wise.

我已经创建了一个表，其中将输入3个状态，Solved，Unsolved和OnHold。我已经创建了以下查询，我想创建条形图，它应该显示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

but it doesn't work, I can see only 1 record as a result. Can anybody help?

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

Pravin

普拉

2 个解决方案

#1

Try this.

尝试这个。

Use CASE expression.

使用CASE表达式。

Query

询问

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`;

#2

You can try like... That working fine

你可以试试......工作正常

SELECT 
  date,
  COUNT(CASE prob_stat WHEN 'Solved' THEN 1 END ) AS Solved, 
  COUNT(CASE prob_stat WHEN 'Unsolved' THEN 1 END) as Unsolved,
  COUNT(CASE prob_stat WHEN 'OnH' THEN 1 END) AS OnHold 
FROM delivery 
GROUP BY date;

#1