mysql - 如何在一个查询中组合六个独立的查询。 TABLE IS SINGLE 但条件不同

Question

我编写了用于获取数据的独立查询。现在我想在一个查询中加入所有查询。如何加入？

1.
select Name, COUNT(name) as Total_Complaint, 
   count(Solved) as SC, COUNT(Pending)as PC 
from helpdesk 
WHERE Solved="yes" OR Pending="yes" 
group by name;

2.
SELECT name,count(name)as Total_Compalint,count(feedback),
   count(feedback)/count(name)*100 as feed_Percent 
from helpdesk 
group by NAME;

3.
select name, sum(feedback)/(count(feedback)*5)*5 as AVG_Feedback 
from helpdesk 
group by name;

4.
select Name, COUNT(name) as Total_Complaint,
   count(Solved) as SC, COUNT(Pending)as PC 
from helpdesk 
group by name;

5.
SELECT name,
   sum(TIMESTAMPDIFF(MINUTE,Request_Time, Close_Time))as Working_Time,
   540-sum(TIMESTAMPDIFF(MINUTE,Request_Time, Close_Time))as VC  
from helpdesk 
group by name ;

6.
select name,
    concat(count(case when etr_meet = 'yes' then 1 else null end) * 100 / count(1), '%')
from `helpdesk`
group by name;

Answer 1

由于您的所有查询都采用相同的模式 - select ... from helpdesk group by name 将它们合并为一个查询很有意义。

请注意，第一个查询的 WHERE 子句 (WHERE Solved="yes"OR Pending="yes") 已使用 CASE 移入 COUNT 语句。

select      Name
           ,COUNT(case when Solved="yes" OR Pending="yes" then name end)    as Total_Complaint
           ,count(Solved)                                                   as SC
           ,COUNT(Pending)                                                  as PC                  
           ,count(name)                                                     as Total_Compalint
           ,count(feedback)
           ,count(feedback)/count(name)*100                                 as feed_Percent 
           ,sum(feedback)/(count(feedback)*5)*5                             as AVG_Feedback   
           ,sum(TIMESTAMPDIFF(MINUTE,Request_Time, Close_Time))             as Working_Time
           ,540-sum(TIMESTAMPDIFF(MINUTE,Request_Time, Close_Time))         as VC        
           ,concat(count(case when etr_meet = 'yes' then 1 else null end) * 100 / count(1), '%') 

from        helpdesk 

group by    name 
;