MYSQL求和两列

Question

我有这样的情况：

SELECT uf.*, SUM(uto.points) AS total_points_live, 
             SUM(utl.points) AS total_points_online,
             (total_points_live + total_points_online) AS total_points
FROM users AS uf    
            LEFT JOIN users_tourney_online AS uto
                ON uto.id_users = uf.id 
            LEFT JOIN users_tourney_live AS utl
                ON utl.id_users = uf.id
GROUP BY uf.id ORDER BY total_points DESC

但是不能工作，因为别名不能使用。

你能帮帮我吗?

致以敬意，

Answer 1

您不能使用在定义别名的同一SELECT子句中定义的别名。

您需要重复该表达式：

SUM(uto.points) + SUM(utl.points) AS total_points

如果SUM%s可以是NULL

COALESCE(SUM(uto.points), 0) + COALESCE(SUM(utl.points), 0) AS total_points

或者，您可以使用子查询：

SELECT t.*, coalesce(total_points_live, 0) + coalesce(total_points_online, 0) total_points
FROM (
    SELECT uf.*, SUM(uto.points) AS total_points_live, SUM(utl.points) AS total_points_online
    FROM ...
    GROUP BY uf.id
) t
ORDER BY total_points desc

不相关的说明：GROUP BY和SELECT *不能很好地配合使用；在GROUP BY子句中枚举所有非聚集列是一种很好的实践(尽管MySQL对此有点松懈)。