展平对sql查询的响应

6l7fqoea 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(310)

我正在尝试用mysql获得一个用于问答表的平面表。这是我的table。
民意测验

PollID | Question| A | B | C | D | E | TimeStamp

花粉React

PollReponseID | PollID | UserID | Answer

在 PollAnswer 我得到五个答案
VARCHAR A, B, C, D, E .
我已经写了一个查询来分组答案 A, B, C, D, E .

select q.Question
     , q.PollID
     , r.Answer
     , count(r.Answer) 
  from pollQuestions q
     , pollResponse r
 where  q.PollID = r.PollID 
 group 
    by r.Answer
     , q.Question
     , q.PollID 
 order 
    by r.PollID;

我的回答如下。

Question | PollID | Answer | count
alpha    |  1     | A     | 2 
alpha    |  1     | B     | 3 
alpha    |  1     | C     | 4 
alpha    |  1     | D     | 0
alpha    |  1     | E     | 0 
betas    |  2     | A     | 3 
betas    |  2     | B     | 4 
betas    |  2     | C     | 4 
betas    |  2     | D     | 6
betas    |  2     | E     | 0

我想把答案说成这样。

Question | PollID | countA | countB | countC | countD | countE
alpha    |  1     | 2      | 2      |  4     |  0     |   0 
betas    |  2     | 3      | 4      |  4     |  6     |   0

我是否可以在不改变表结构的情况下实现这一点？
任何指示都将不胜感激。

sql mysql

来源：https://stackoverflow.com/questions/53246081/flatten-a-response-to-an-sql-query

2条答案

按热度按时间

ghhaqwfi1#

您可以尝试使用条件聚合函数。

select 
    pollQuestions.Question, 
    pollQuestions.PollID, 
    count(CASE WHEN pollResponse.Answer ='A' THEN 1 END) countA,
    count(CASE WHEN pollResponse.Answer ='B' THEN 1 END) countB,
    count(CASE WHEN pollResponse.Answer ='C' THEN 1 END) countC,
    count(CASE WHEN pollResponse.Answer ='D' THEN 1 END) countD,
    count(CASE WHEN pollResponse.Answer ='E' THEN 1 END) countE
from pollQuestions 
JOIN pollResponse on pollQuestions.PollID = pollResponse.PollID  
group by 
    pollQuestions.Question,
    pollQuestions.PollID  
order by  
    pollResponse.PollID;

笔记
我会用 JOIN 而不是逗号 , 因为 JOIN 语法比 , 关于连接两个表。

赞(0）回复(0）举报 2021-06-18

w3nuxt5m2#

你可以用 group by 与 sum(case when...) 如下所示：

select pq.Question, pq.PollID,
       sum(case when pr.Answer = 'A' then 1 else 0 end) as 'countA',
       sum(case when pr.Answer = 'B' then 1 else 0 end) as 'countB',
       sum(case when pr.Answer = 'C' then 1 else 0 end) as 'countC',
       sum(case when pr.Answer = 'D' then 1 else 0 end) as 'countD',
       sum(case when pr.Answer = 'E' then 1 else 0 end) as 'countE'
from PollQuestion pq
left join PollResponse pr on pq.PollID = pr.PollID
group by pq.Question, pq.PollID

赞(0）回复(0）举报 2021-06-18

我来回答

展平对sql查询的响应

2条答案

相关问题

热门标签

最新问答