mysql - 如何将结果集行显示为列

Question

我的表 price_matrix 是

id service_id  deadline  edu_level paper_stand  price
------------------------------------------------------
1     5         20         GCES       A          50
2     5         10         GCSE       A          30
3     5         20         GCSE       B          10
2     5         10         GCSE       B          40

我怎样才能写一个查询来得到以下格式的结果

deadline   service_id   paper_stand A   paper_stand B edu_level
-----------------------------------------------------------------
20            5              50               10          GCES 
10            5              30                40          GCSE

我正在使用的查询是

SELECT `paper_stand` , `deadline` , `price`
FROM `price_matrix`
WHERE `edu_level` = 'GCSE/ A Levels' && `service_id` =5
ORDER BY `paper_stand` , `deadline`

我正在使用 MYSQL

谁能帮我解释一下如何获得所需格式的结果？

谢谢

Answer 1

您可以JOIN 表本身。

SELECT a.deadline,
       a.service_id,
       a.price AS paper_stand_a,
       b.price AS paper_stand_b,
       a.edu_level
FROM price_matrix AS a JOIN price_matrix AS b
    ON a.deadline = b.deadline AND a.paper_stand = 'A' AND b.paper_stand = 'B'
GROUP BY a.deadline;

您也可以在没有连接的情况下执行此操作。我使用 MAX 只是为了获得价格的非 NULL 值。可能有更好的方法来完成这部分。

SELECT deadline,
       service_id,
       MAX(IF(paper_stand = 'A', price, NULL) AS paper_stand_a,
       MAX(IF(paper_stand = 'B', price, NULL) AS paper_stand_b,
       edu_level
FROM price_matrix
GROUP BY deadline;