我有这样的情况:
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
但不工作,因为别名不能使用。
你能帮助我吗?
问候,
您不能使用相同的定义的别名SELECT
它被定义条款。
您将需要重复表达式:
SUM(uto.points) + SUM(utl.points) AS total_points
如果SUM
可以彼此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是它有些松懈)。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句