mysql - MySQL投票计数每一行每一列

与SQL: Count each row value in a column and return count into multiple column不同

我的行也不同。
我想要这样的投票计数结果。

CONTENT_NO  CONTENT_TEXT  one     two     three    four(ANSWER_SCORE)
1           How often?    0       1       0        1
2           How fast?     0       1       0        1
3           How long?     0       0       2        0
4           How much?     1       1       0        0

我有2张桌子

question_content
CONTENT_NO  CONTENT_TEXT  TEST_PAPER_NO
1           How often?    1
2           How fast?     1
3           How long?     1
4           How much?     1


question_content_answer
ANSWER_NO  CONTENT_NO  TEST_PAPER_NO  ANSWER_SCORE  PERSON_NO
1          1           1              4             1
2          2           1              4             1
3          3           1              3             1
4          4           1              2             1

5          1           1              2             2
6          2           1              2             2
7          3           1              3             2
8          4           1              1             2

我试过了

SELECT question_content.CONTENT_NO,
       question_content.CONTENT_TEXT,

       (SELECT COUNT(ANSWER_SCORE)
          FROM (SELECT ANSWER_SCORE
                  FROM question_content_answer
                 WHERE TEST_PAPER_NO= '1'
               ) as qa
          WHERE ANSWER_SCORE = '1'
            AND CONTENT_NO = qa.CONTENT_NO
          GROUP BY CONTENT_NO
       ) as one,
       (SELECT COUNT(ANSWER_SCORE)
          FROM (SELECT ANSWER_SCORE
                  FROM question_content_answer
                 WHERE TEST_PAPER_NO= '1'
               ) as qa
          WHERE ANSWER_SCORE = '2'
            AND CONTENT_NO = qa.CONTENT_NO
          GROUP BY CONTENT_NO
       ) as two,
       (SELECT COUNT(ANSWER_SCORE)
          FROM (SELECT ANSWER_SCORE
                  FROM question_content_answer
                 WHERE TEST_PAPER_NO= '1'
               ) as qa
          WHERE ANSWER_SCORE = '3'
            AND CONTENT_NO = qa.CONTENT_NO
          GROUP BY CONTENT_NO
       ) as three

  FROM question_content_answer
 INNER JOIN question_content
 USING (CONTENT_NO)
 WHERE question_content.TEST_PAPER_NO= '1'

但这会计算每一行中的所有问题。

如何计算每一行？

最佳答案

就像SQL: Count each row value in a column and return count into multiple column

此代码可以正常工作。

SELECT question_content.CONTENT_NO,
       question_content.CONTENT_TEXT,

       COUNT(CASE WHEN ANSWER_SCORE = '1' THEN 1 END) AS one,
       COUNT(CASE WHEN ANSWER_SCORE = '2' THEN 1 END) AS two,
       COUNT(CASE WHEN ANSWER_SCORE = '3' THEN 1 END) AS three,
       COUNT(CASE WHEN ANSWER_SCORE = '4' THEN 1 END) AS four

  FROM question_content_answer

 INNER JOIN question_content
 USING (CONTENT_NO)
 WHERE question_content.TEST_PAPER_NO = '1'
 GROUP BY question_content.CONTENT_NO

关于mysql - MySQL投票计数每一行每一列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44536100/