在一个查询中计算多列

小编典典

在一个查询中计算多列

sql

伙计们，我有四个疑问：

查询1：

select 
satisfaction_score,count(satisfaction_score) as Satisfaction_count
from j_survey_response
where satisfaction_score != 0
group by satisfaction_score

输出将是

查询2：

select 
response_score,count(response_score) as response_count
from j_survey_response
where response_score != 0
group by response_score

输出

response_score response_count
1 28
2 8
3 42
4 250
5 980

查询3：

select 
responder_score,count(responder_score) as responder_count
from j_survey_response
where responder_score != 0
group by responder_score

输出

responseer_score responseer_count
1 24
2 3
3 30
4236
5987

查询＃4：

select 
service_score,count(service_score) as service_count
from j_survey_response
where service_score != 0
group by service_score

输出

service_score service_count
1 22
2 2
3 34
4270
5966

但是我需要以下输出

分数满意度_计数响应_计数响应者_计数服务_计数
1 5 28 24 22
2 8 8 3 2
3 97 42 30 34
4329250236270
5859980986966

阅读 135

2021-04-15

共1个答案

小编典典

您可以UNION ALL单独查询并在结果集上应用条件聚合：

select score,
       max(case when type = 'satisfaction' then count end) as satisfaction_count,
       max(case when type = 'response' then count end) as response_count,
       max(case when type = 'responder' then count end) as responder_count,
       max(case when type = 'service' then count end) as service_count             
from (
  select satisfaction_score as score, 
         count(satisfaction_score) as count, 
         'satisfaction' as type
  from j_survey_response
  where satisfaction_score != 0
  group by satisfaction_score

  union all

  select response_score,
         count(response_score) as count, 'response' as type
  from j_survey_response
  where response_score != 0
  group by response_score

  union all

  select responder_score,
         count(responder_score) as count, 'responder' as type
  from j_survey_response
  where responder_score != 0
  group by responder_score

  union all

  select service_score,
         count(service_score) as count, 'service' as type
  from j_survey_response
  where service_score != 0
  group by service_score) as t
group by score

2021-04-15