mysql - 计算来自多列的不同值

Question

我有这张表:

╔════════════════╤═══════════════════╤═══════════╤═══════════╗
║ question1      │ question2         │ question3 │ question4 ║
╠════════════════╪═══════════════════╪═══════════╪═══════════╣
║ Agree          │ Disagree          │ Agree     │ Disagree  ║
╟────────────────┼───────────────────┼───────────┼───────────╢
║ Strongly Agree │ Strongly Disagree │ Agree     │ Disagree  ║
╚════════════════╧═══════════════════╧═══════════╧═══════════╝

我正在尝试使用 COUNT() 编写一个查询，它显示每个问题的响应数量，如下所示:

╔══════════╤════════════════╤═══════╤══════════╤═══════════════════╗
║ Question │ Strongly Agree │ Agree │ Disagree │ Strongly Disagree ║
╠══════════╪════════════════╪═══════╪══════════╪═══════════════════╣
║ Q1       │ 1              │ 1     │ 0        │ 0                 ║
╟──────────┼────────────────┼───────┼──────────┼───────────────────╢
║ Q2       │ 0              │ 0     │ 1        │ 1                 ║
╟──────────┼────────────────┼───────┼──────────┼───────────────────╢
║ Q3       │ 0              │ 2     │ 0        │ 0                 ║
╟──────────┼────────────────┼───────┼──────────┼───────────────────╢
║ Q4       │ 0              │ 0     │ 2        │ 0                 ║
╚══════════╧════════════════╧═══════╧══════════╧═══════════════════╝

我尝试了几次查询，但总是给出错误的结果。任何帮助，将不胜感激。谢谢。

Answer 1

不确定您为什么选择按照显示的方式构建表格，但如果您可以灵活地更改它，我建议您这样做。使用现在呈现的结构，您不仅在获得所需结果的正确查询时遇到问题，而且您的结构也不适用于在不更新数据库模式的情况下添加新问题。

如果您不能修改表结构<强> SQL DEMO

SELECT 'Q1' as Question , 
        Count(CASE WHEN Question1 = 'Strongly Agree'    THEN 1 END) AS 'Strongly Agree',
        Count(CASE WHEN Question1 = 'Agree'             THEN 1 END) AS 'Agree',
        Count(CASE WHEN Question1 = 'Disagree'          THEN 1 END) AS 'Disagree',
        Count(CASE WHEN Question1 = 'Strongly Disagree' THEN 1 END) AS 'Strongly Disagree'
FROM QandR
UNION ALL
SELECT 'Q2' as Question , 
        Count(CASE WHEN Question2 = 'Strongly Agree'    THEN 1 END) AS 'Strongly Agree',
        Count(CASE WHEN Question2 = 'Agree'             THEN 1 END) AS 'Agree',
        Count(CASE WHEN Question2 = 'Disagree'          THEN 1 END) AS 'Disagree',
        Count(CASE WHEN Question2 = 'Strongly Disagree' THEN 1 END) AS 'Strongly Disagree'
FROM QandR
UNION ALL
SELECT 'Q3' as Question , 
        Count(CASE WHEN Question3 = 'Strongly Agree'    THEN 1 END) AS 'Strongly Agree',
        Count(CASE WHEN Question3 = 'Agree'             THEN 1 END) AS 'Agree',
        Count(CASE WHEN Question3 = 'Disagree'          THEN 1 END) AS 'Disagree',
        Count(CASE WHEN Question3 = 'Strongly Disagree' THEN 1 END) AS 'Strongly Disagree'
FROM QandR
UNION ALL
SELECT 'Q4' as Question , 
        Count(CASE WHEN Question4 = 'Strongly Agree'    THEN 1 END) AS 'Strongly Agree',
        Count(CASE WHEN Question4 = 'Agree'             THEN 1 END) AS 'Agree',
        Count(CASE WHEN Question4 = 'Disagree'          THEN 1 END) AS 'Disagree',
        Count(CASE WHEN Question4 = 'Strongly Disagree' THEN 1 END) AS 'Strongly Disagree'
FROM QandR

如果你可以改变结构

这是我的建议:

2 个表:问题和问题响应

1. 问题有 2 列
   1. id(整数；自增)
   2. 问题(varchar)

1. QuestionRresponse 有 3 列
   1. id(整数；自增)
   2. QuestionId (int; FK to Question:id)
   3. 响应(varchar)

然后您可以使用此查询和输出获取您要查找的数据:

SELECT q.Question, qr.Response, Count(qr.Response) as Count
FROM  `Question` q
LEFT JOIN QuestionResponse qr ON q.id = qr.QuestionId
GROUP BY q.Question,qr.Response