mysql SUM( column )不会对列联合上的元素求和

Question

谁能告诉我为什么会有这样的代码：

SELECT t.prob FROM 
(SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els) AS e2) AS t
 UNION ALL (SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els2 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els2) AS e2)
 UNION ALL (SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els3 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els3) AS e2);

是否生成与this相同的输出，而不是'prob‘的和？

SELECT SUM(t.prob) FROM 
(SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els) AS e2) AS t
 UNION ALL (SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els2 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els2) AS e2)
 UNION ALL (SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els3 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els3) AS e2);

(代码基本上为三个表els、els2和els3中的每个表创建了一个只包含一个值的列prob，然后将这三个表组合成一个列，我想从该列中获得3个元素的总和)

我想出了另一个代码。它是有效的，它是一个更清晰的声明，所以不用担心：

SELECT SUM(t.evaled/IFNULL(NULLIF(t.total,0),1)) as sumatory FROM
(SELECT evaled,total FROM
 (SELECT COUNT(*) AS evaled FROM els WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els) AS e2
UNION ALL SELECT * FROM
 (SELECT COUNT(*) AS evaled FROM els2 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els2) AS e2
UNION ALL SELECT * FROM
 (SELECT COUNT(*) AS evaled FROM els3 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els3) AS e2) as t;

Answer 1

我不确定你的解决方案出了什么问题，但我想我知道你想要实现的是什么。像这样的方法怎么样：

SELECT 
 (SELECT COUNT(*)/(SELECT COUNT(*) FROM els) AS evaled FROM els WHERE evals=0)
 +
 (SELECT COUNT(*)/(SELECT COUNT(*) FROM els2) AS evaled FROM els2 WHERE evals=0) 
 +
 (SELECT COUNT(*)/(SELECT COUNT(*) FROM els3) AS evaled FROM els3 WHERE evals=0)
 /3

编辑:如果你想要平均值，我想你最后需要一个/3。