php - MySQL : Getting the sum of multiple columns

Question

这对我来说非常冲突，让我尽我所能地解释一下。这就像一个 2 层的计算。第一层是我需要获得总 A 、总 B 、总 C 和总 D 。但是，要获得总 A ，我需要计算一些列，以及总 B、C 和 D 。第二层是显示并获取A、B、C、D总计的和。这是一堆表。

对于第一个表。

 tbl_criteria
 ------------------------
 crit_id | criteria_name
    16   |    sports
    17   |    formal
    18   |    talent
    19   |    nothing

tbl_criteria 有一个子标准

 tbl_sub_criteria
 ----------------------
 sub_crit_id | crit_id | sub_crit_name
      22     |   16    |    originality
      23     |   16    |    audience Impact
      24     |   18    |    Appeal
      25     |   18    |    Stage Presence

第三席为评委。

tbl_judges
 ------------------------
judge_id  |   judge_name
   61     |    first
   62     |    second
   63     |    third

参赛者表假设有 2 名参赛者

tbl_cotestant
-----------------------------------------------
con_id  |  contestant_number | contestant_name |
   1    |         1          |      john       |
   2    |         2          |       sy        |

最后一个表，这是构建的表

tbl_score
--------------------------------------------------
score_id | crit_id |  sub_crit_id |   judge_id | con_id | contestant_number |   score
   1     |    16   |      22      |      61    |   1    |        1          |     25  
   2     |    16   |      22      |      61    |   2    |        2          |     25
   3     |    16   |      22      |      62    |   1    |        1          |     25  
   4     |    16   |      22      |      62    |   2    |        2          |     73
   5     |    16   |      22      |      63    |   1    |        1          |     70  
   6     |    16   |      22      |      63    |   2    |        2          |     80
   7     |    16   |      23      |      61    |   1    |        1          |     25  
   8     |    16   |      23      |      61    |   2    |        2          |     25
   9     |    16   |      23      |      62    |   1    |        1          |     25  
   10    |    16   |      23      |      62    |   2    |        2          |     73
   11    |    18   |      23      |      63    |   1    |        1          |     70   
   12    |    16   |      23      |      63    |   2    |        2          |     80
   13    |    18   |      24      |      61    |   1    |        1          |     25  
   14    |    18   |      24      |      61    |   2    |        2          |     25
   15    |    18   |      24      |      62    |   1    |        1          |     25  
   16    |    18   |      24      |      62    |   2    |        2          |     73
   17    |    18   |      24      |      63    |   1    |        1          |     70  
   18    |    18   |      24      |      63    |   2    |        2          |     80
   19    |    18   |      25      |      61    |   1    |        1          |     25  
   20    |    18   |      25      |      61    |   2    |        2          |     25
   21    |    18   |      25      |      62    |   1    |        1          |     25  
   22    |    18   |      25      |      62    |   2    |        2          |     73
   23    |    18   |      25      |      63    |   1    |        1          |     70  
   24    |    18   |      25      |      63    |   2    |        2          |     80
   25    |    17   |     null     |      61    |   1    |        1          |     25  
   26    |    17   |     null     |      61    |   2    |        2          |     25
   27    |    17   |     null     |      62    |   1    |        1          |     25  
   28    |    17   |     null     |      62    |   2    |        2          |     73
   29    |    17   |     null     |      63    |   1    |        1          |     70  
   30    |    17   |     null     |      63    |   2    |        2          |     80
   31    |    19   |     null     |      61    |   1    |        1          |     25  
   32    |    19   |     null     |      61    |   2    |        2          |     25
   33    |    19   |     null     |      62    |   1    |        1          |     25  
   34    |    19   |     null     |      62    |   2    |        2          |     73
   35    |    19   |     null     |      63    |   1    |        1          |     70  
   36    |    19   |     null     |      63    |   2    |        2          |     80

第一层输出是这样的，得到A、B、C、D的总和。

总数应显示为

(criteria 16 has two sub-criterias 22, 23 , that means it will be x2)
con_num | contestant_name | 16_judge_61 | 16_judge_62 | 16_judge_63 | total a
    1   |       john      |       50    |      25     |     140     |   215   
    2   |       sy        |       50    |      146     |     160      |  365

如上表所示，John 在第 16 条标准中总共得到了 215 分(crit_id 16)。 sy 在第 16 项标准中总共得到了 365 分。

所以，我的表中有 4 个标准 16、17、18、19。这是我的问题，这意味着我需要一一查询以获得每个总输出，就像

con_num | contestant_name | 17_judge_61 | 17_judge_62 | 17_judge_63 | total b
    1   |       john      |       25    |      25     |     70      |  120     
    2   |       sy        |       25    |      73     |     80      |  178

 (criteria 18 has a sub criteria 24,25 that means it will x2)
 con_num | contestant_name | 18_judge_61 | 18_judge_62 | 18_judge_63 | total c
    1   |       john      |       50    |      50     |     140      |  240     
    2   |       sy        |       50    |      146    |     160      |  356


 con_num | contestant_name | 19_judge_61 | 19_judge_62 | 19_judge_63 | total d
    1   |       john      |       25    |      25     |     70      |  120     
    2   |       sy        |       25    |      73     |     80      |  178

我需要在一个查询中做到这一点，只有当一一获得总A，B C时我才能做到这一点。但是我需要一个执行这种输出的单查询。我怎样才能实现这个输出？

total_a, b , c, d 相当于 crit_id 16, 17, 18, 19

con_num | contestant_name | 16total_a | 17total_b | 18total_c | 19total_d | Grand_total
   1    |       john      |   215     |   120     |   240     |  120      |   695 
   2    |        jy       |   365     |   178     |   356     |  178      |   1077 

这个查询，我还在学习逻辑

 SELECT DISTINCT(a.contestant_number) as con_num, a.contestant_name,

//Getting first the sum of total a, Notice criteria 16 has a sub criteria 22, 23
SUM(CASE WHEN s.crit_id='16' AND s.sub_crit_id = 22 AND s.judge_id='61' THEN s.score END) as 16_judge_61,
SUM(CASE WHEN s.crit_id='16' AND s.sub_crit_id = 22 AND s.judge_id='62' THEN s.score END) as 16_judge_62,
SUM(CASE WHEN s.crit_id='16' AND s.sub_crit_id = 22 AND s.judge_id='63' THEN s.score END) as 16_judge_63,

SUM(CASE WHEN s.crit_id='16' AND s.sub_crit_id = 23 AND s.judge_id='61' THEN s.score END) as 16_judge_61,
SUM(CASE WHEN s.crit_id='16' AND s.sub_crit_id = 23 AND s.judge_id='62' THEN s.score END) as 16_judge_62,
SUM(CASE WHEN s.crit_id='16' AND s.sub_crit_id = 23 AND s.judge_id='63' THEN s.score END) as 16_judge_63,

SUM(CASE WHEN s.crit_id='16' AND s.judge_id in (61, 62, 63) THEN s.score END) as 'total a'

//Criteria 17 has no sub criteria                                                    
SUM(CASE WHEN s.crit_id='17' AND s.judge_id='61' THEN s.score END) as 16_judge_61,
SUM(CASE WHEN s.crit_id='17' AND s.judge_id='62' THEN s.score END) as 16_judge_62,
SUM(CASE WHEN s.crit_id='17' AND s.judge_id='63' THEN s.score END) as 16_judge_63,


SUM(CASE WHEN s.crit_id='17' AND s.judge_id in (61, 62, 63) THEN s.score END) as 'total b'                                                    

//And iterate again for the 2 more criteria
//
FROM tbl_score s
INNER JOIN tbl_contestant a ON s.contestant_number = a.contestant_number
INNER JOIN tbl_judges j ON j.judge_id = s.judge_id
WHERE c.gender = 'male' and c.con_id = s.con_id
GROUP BY s.contestant_number
ORDER By `Grand toal' DESC";                                                   

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

仅获取一个总和 http://sqlfiddle.com/#!9/9efa5/1

Answer 1

将查询放入子查询中，然后将各列加在一起以获得总计。

SELECT x.*, `total a` + `total b` + `total c` AS `Grand Total`
FROM (put your query here) AS x