mysql - MySQL中的交叉表查询

Question

我创建了一个 sql select 查询，但无法正确显示记录。

表:

gradeid | usrname | reviewmonth | program | total_score | pae
--------------------------------------------------------------
151     | smithj  |      2      | math    | 100%        | 100%
152     | smithj  |      2      | math    |  95%        | 100%
153     | smithj  |      3      | math    |  80%        | 100%
154     | jonesm  |      3      | math    |  79%        | 79% 
155     | jonesj  |      2      | art     |  100%       | 100%

我创建的用于显示信息的查询是

SELECT reviewmonth, 
ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) AS January
FROM vwscore 
WHERE program = 'Math' AND reviewmonth = 1 
UNION ALL
SELECT reviewmonth, 
ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) AS February
FROM vwscore 
WHERE program = 'Math' AND reviewmonth = 2 
UNION ALL
SELECT reviewmonth, 
ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) AS March
FROM vwscore 
WHERE program = 'Math' AND reviewmonth =  3
UNION ALL

查询返回:不幸的是我需要水平显示记录。

reviewmonth | January 
----------------------
      1     |  91.94
      2     |  94.86
      3     |  89.89 

期望的结果:

January | February | March
--------------------------
   91.94|   94.86  |  89.89

我尝试了不同的查询来显示答案。我尝试使用CASE，但它似乎只显示1条记录。感谢你的帮助!

Answer 1

此查询满足您问题的横向需求。

我不关心你想做什么。

请确定这是否适合您。

SELECT 
CASE WHEN  program = 'Math' AND reviewmonth = 1 THEN
        ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) 
        ELSE 'NULL' END AS January,
CASE WHEN program= 'Math' AND reviewmonth = 2 THEN
        ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) 
    ELSE 'NULL' END AS February,

CASE WHEN program= 'Math' AND reviewmonth = 3 THEN
        ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2)
    ELSE 'NULL' END AS March
FROM vwscore
WHERE program='Math'

编辑:我并不关心你的期望。现在你会得到我希望的正确结果。

SELECT
ROUND(t.January/t.JanuaryCount,2) AS January,
ROUND(t.February/t.FebruaryCount,2) AS February,
ROUND(t.March/t.MarchCount,2) AS March
FROM 
(
    SELECT
            SUM(CASE WHEN reviewmonth=1 THEN IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score) ELSE 0 END) AS January,
            SUM(CASE WHEN reviewmonth=1 THEN 1 ELSE 0 END) AS JanuaryCount,

            SUM(CASE WHEN reviewmonth=2 THEN IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score) ELSE 0 END) AS February,
            SUM(CASE WHEN reviewmonth=2 THEN 1 ELSE 0 END) AS FebruaryCount,

            SUM(CASE WHEN reviewmonth=3 THEN IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score) ELSE 0 END) AS March,
            SUM(CASE WHEN reviewmonth=3 THEN 1 ELSE 0 END) AS MarchCount

    FROM 
    vwscore
    WHERE program='Math'
) AS t