自连接以比较来自同一个表的季度数据？

Question

我正在帮助一家小型农村医疗系统为他们的业务添加一些数据库。他们目前跟踪每个季度报告的一些运营统计数据。

我想把这些回应的要点放在一个表中，并从中进行历史比较。我将有一些额外的表用于规范化，但这里是主要的一个。

+-------+----------+-------------+----------+
| year  | quarter  | question_id | response |
+-------+----------+-------------+----------+
| 2000  | 1        | 100         | 50       |
+-------+----------+-------------+----------+
| 2000  | 2        | 100         | 100      |
+-------+----------+-------------+----------+
| 2000  | 1        | 200         | 75       |
+-------+----------+-------------+----------+
| 2000  | 2        | 200         | 25       |
+-------+----------+-------------+----------+

我正在寻找的输出是将每个问题的季度响应并排放在一起，这样我就可以更好地计算变化，并简化我的PHP处理。以下是所需的输出：

+-------------+----------------------+----------------------+
| question_id | 1st quarter response | 2nd quarter response |
+-------------+----------------------+----------------------+
| 100         | 50                   | 100                  |
+-------------+----------------------+----------------------+
| 200         | 75                   | 25                   |
+-------------+----------------------+----------------------+

我刚接触MySQL (使用SQL5)，我唯一能想到的就是我需要一个自连接。我试过了，但似乎就是搞不懂。对于这种历史分析，我是否采用了正确的表结构方法？

任何提示都会对我们的项目有很大的帮助和帮助！

谢谢!

Answer 1

您可以group by问题，然后使用case和max的组合来选择该季度的答案。

select  question_id
,       max(case when quarter = 1 then question_responses end) as Q1
,       max(case when quarter = 2 then question_responses end) as Q2
,       max(case when quarter = 3 then question_responses end) as Q3
,       max(case when quarter = 4 then question_responses end) as Q4
from    question_responses
where   year = 2000
group by
        question_id

在本例中，max实际上并没有max任何东西，它最终选择了case返回值的唯一一个季度。

Answer 2

假设你想要得到特定年份的输出，例如。2000年：

SELECT
    question_id,
    (SELECT response FROM question_responses WHERE year = qr.year AND question_id = qr.question_id AND quarter = 1) AS qrt_resp_1,
    (SELECT response FROM question_responses WHERE year = qr.year AND question_id = qr.question_id AND quarter = 2) AS qrt_resp_2
    FROM question_responses AS qr
    WHERE year = 2000
    GROUP BY question_id