MySQL - 行到列并保留 NULL

Question

我们在 MySQL 数据库(MySQL 5.6)中有下表(TEST2):

 TEAM_ID,MEMBER_ID,TYPE,SCORE
 1,2,A,150
 1,3,B,200
 1,1,B,50
 1,1,A,100
 1,2,B,NULL

我们尝试根据 TYPE 列转换/透视上表:

如果 TYPE 列的值 == A，则将 SCORE 列中的值移动到名为 A_SCORE 的新列中。如果 SCORE 列中的值为 NULL，则新的 A_SCORE 列中应显示 NULL。

如果 TYPE 列的值 == B，则将 SCORE 列中的值移动到名为 B_SCORE 的新列中。如果 SCORE 列中的值为 NULL，则新的 B_SCORE 列中应显示 NULL。

下表是我们正在寻找的表(想要的表):

 TEAM_ID,MEMBER_ID,A_SCORE,B_SCORE,A_SCORE_MINUS_B_SCORE
 1,1,100,50,50
 1,2,150,NULL,NULL
 1,3,0,200,-200

我们尝试了以下查询

 SELECT TEAM_ID,MEMBER_ID,A_SCORE,B_SCORE,SUM(A_SCORE-B_SCORE) AS ACTUAL_MINUS_B_SCORE FROM
  (SELECT TEAM_ID,MEMBER_ID,

 CASE
 WHEN SCORE IS NULL
 THEN NULL
 ELSE SUM(if(TYPE = 'A', SCORE,0) )
 END A_SCORE,

 CASE
 WHEN SCORE IS NULL
 THEN NULL
 ELSE SUM(if(TYPE = 'B', SCORE,0) )
 END B_SCORE
 FROM TEST2
 GROUP BY TEAM_ID,MEMBER_ID,SCORE) AS A
 GROUP BY TEAM_ID,MEMBER_ID,A_SCORE,B_SCORE);

它返回了我们不想要的东西:

 TEAM_ID,MEMBER_ID,A_SCORE,B_SCORE,A_SCORE_MINUS_B_SCORE
 1,1,0,50,-50
 1,1,100,0,100
 1,2,0,0,0
 1,2,150,0,150
 1,3,0,200,-200

如果我们尝试以下操作，它会生成一个接近我们想要的表，但它不会返回任何 NULL 值。

 SELECT TEAM_ID,MEMBER_ID,A_SCORE,B_SCORE,SUM(A_SCORE-B_SCORE) AS A_SCORE _MINUS_B_SCORE FROM
  (SELECT TEAM_ID,MEMBER_ID,

 SUM(if(TYPE = 'A', SCORE,0) ) AS A_SCORE,

 SUM(if(TYPE = 'B', SCORE,0) )AS B_SCORE
 FROM TEST2
 GROUP BY TEAM_ID,MEMBER_ID) AS A
 GROUP BY TEAM_ID,MEMBER_ID,A_SCORE,B_SCORE;

上述查询的结果:

 TEAM_ID,MEMBER_ID,A_SCORE,B_SCORE,A_SCORE_MINUS_B_SCORE
 1,1,100,50,50
 1,2,150,0,0
 1,3,0,200,-200

任何大师都可以指导如何使用 MySQL 在这种情况下生成所需的表吗？ SQL fiddle 在这里是为了您的方便。

http://sqlfiddle.com/#!9/cfe7a1/1

谢谢!

Answer 1

试试这个；)

SELECT TEAM_ID, MEMBER_ID, A_SCORE, B_SCORE, A_SCORE - B_SCORE AS A_SCORE_MINUS_B_SCORE
FROM (
    SELECT
        TEAM_ID, MEMBER_ID,
        CASE
            WHEN A_SCORE IS NULL AND NOT EXISTS (
                SELECT 1 FROM TEST2 
                WHERE TEAM_ID = T1.TEAM_ID 
                AND MEMBER_ID = T1.MEMBER_ID
                AND TYPE = 'A'
            ) THEN 0 ELSE A_SCORE END AS A_SCORE,
        CASE
            WHEN B_SCORE IS NULL AND NOT EXISTS (
                SELECT 1 FROM TEST2 
                WHERE TEAM_ID = T1.TEAM_ID 
                AND MEMBER_ID = T1.MEMBER_ID
                AND TYPE = 'A'
            ) THEN 0 ELSE B_SCORE END AS B_SCORE
    FROM (
        SELECT
           TEAM_ID, MEMBER_ID,
           MAX(CASE WHEN TYPE = 'A' THEN SCORE END) AS A_SCORE,
           MAX(CASE WHEN TYPE = 'B' THEN SCORE END) AS B_SCORE
        FROM TEST2
        GROUP BY TEAM_ID, MEMBER_ID
    ) T1
)T

SQLFiddle demo here