如何在一个查询中组合六个独立查询。表是单一的，但条件不同

I wrote the independent query for fetching data. Now I want to join all query in one query. How to join?

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

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;

1 个解决方案

#1

Since all your query are in the same pattern - select ... from helpdesk group by name it makes a lot of sense to merge them into a single query.

由于您的所有查询都采用相同的模式 - 从帮助台组中按名称选择...将它们合并到单个查询中非常有意义。

Note that the WHERE clause from the 1st query (WHERE Solved="yes" OR Pending="yes") was moved into the COUNT using CASE statement.

请注意，第一个查询的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 
;

1 个解决方案

#1

更多相关文章

随机推荐