SUM与支点来计算综合得分(SUM with a pivot to calculate overal

从另一个问题，我得到这个查询来获取我的考试成绩总结了正确：

SELECT callSign,event, SUM(score) 
  FROM scores LEFT JOIN candidates 
ON scores.candidateID=candidates.id 
  WHERE candidateID IN 
    (SELECT id 
      FROM candidates 
    WHERE assessmentID='1321') 
  GROUP BY event, callSign
  ORDER BY candidateID,event

我得到的，看起来像数据：

callSign    event           TotalScore
Y209    Bridge                  45
Y209    PSA                     3
Y209    Team Analyst Exam       40
X125    PSA                     1
X125    Team Analyst Exam       38
V023    Amazing Race Planning   37

我需要的是像数据：

callSign      Bridge   PSA   Amazing Race Planning     Team Analyst Exam   
V023                                37
Y209           45       3                                   40         
X125                    1                                   38        

表结构

`events`
id  event
1   PSA
2   Bridge
30  Stress Board
25  Amazing Race Planning
26  Amazing Race Execution

`scores`
id  candidateID     event            rubric            category                         score   comment
1   18       Team Analyst Exam  Team Leader Rubric  Organizes and Tasks Team Members    3    
2   18       Team Analyst Exam  Team Leader Rubric  Roles and Responsibilities          5    
3   18       Team Analyst Exam  Team Leader Rubric  Backward Planning                   5    
4   18       Team Analyst Exam  Team Leader Rubric  Time Management

`candidates`    
id  firstName   middleInitial   lastName    callSign    service     rank    sex     height  weight  assessmentID    currentlyAssessing  hired

呼号就是X125会去

Answer 1:

既然你正在使用MySQL，以透视数据成列，您将需要使用聚合函数与CASE表达式：

SELECT callSign, 
    SUM(case when event = 'Bridge' then score else 0 end) as Bridge,
    SUM(case when event = 'PSA' then score else 0 end) as PSA,
    SUM(case when event = 'Amazing Race Planning' then score else 0 end) As AmazingRacePlanning,
    SUM(case when event = 'Team Analyst Exam' then score else 0 end) as TeamAnalystExam
FROM scores 
LEFT JOIN candidates 
    ON scores.candidateID=candidates.id 
WHERE candidateID IN (SELECT id 
                      FROM candidates 
                      WHERE assessmentID='1321') 
GROUP BY callSign

如果你有数目不详的events ，那么你将不得不使用准备好的语句来生成动态SQL：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN event = ''',
      event,
      ''' THEN score END) AS `',
      event, '`'
    )
  ) INTO @sql
FROM scores 
LEFT JOIN candidates 
    ON scores.candidateID=candidates.id;


SET @sql 
  = CONCAT('SELECT callSign, ', @sql, ' 
           FROM scores 
            LEFT JOIN candidates 
                ON scores.candidateID=candidates.id 
            WHERE candidateID IN (SELECT id 
                                  FROM candidates 
                                  WHERE assessmentID=''1321'') 
            GROUP BY callSign');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

编辑＃1，如果你的events都存储在一个单独的表，那么你可以使用以下方法来生成动态结果：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN event = ''',
      event,
      ''' THEN score END) AS `',
      event, '`'
    )
  ) INTO @sql
FROM events;



SET @sql 
  = CONCAT('SELECT callSign, ', @sql, ' 
           FROM scores 
            LEFT JOIN candidates 
                ON scores.candidateID=candidates.id 
            WHERE candidateID IN (SELECT id 
                                  FROM candidates 
                                  WHERE assessmentID=''1321'') 
            GROUP BY callSign');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

请参阅SQL拨弄演示

文章来源: SUM with a pivot to calculate overall score