求和与求和计算总分 [英] SUM with a pivot to calculate overall score

问题描述

从另一个问题中，我得到了这个查询，以将我的分数正确汇总:

From another question I got this query to get my scores summed up properly:

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

我需要的是像这样的数据:

What I need is data like:

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

callSign是X125的去向

callSign is where X125 would go

推荐答案

由于使用的是MySQL，为了将数据转换为列，您将需要使用带有CASE表达式的聚合函数:

Since you are using MySQL, in order to pivot the data into columns, you will need to use an aggregate function with a CASE expression:

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:

If you have an unknown number of events, then you will have to use a prepared statement to generate dynamic 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存储在单独的表中，则可以使用以下命令生成动态结果:

Edit #1, if your events are stored in a separate table, then you can use the following to generate the dynamic result:

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提琴

这篇关于求和与求和计算总分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！