mysql - 如何用 WHERE 子句求和两个子查询

Question

我有这个问题:

SELECT *,
COALESCE((
    SELECT SUM(sum_points)
    FROM (
        SELECT SUM(user_points.points) AS sum_points FROM user_points
        WHERE user_points.user_id = user.user_id
        UNION ALL
        SELECT SUM(used_points.points) AS sum_points FROM used_points
        WHERE used_points.user_id = user.user_id
    ) t
), 0) AS total_points_credit
FROM user

但是我得到这个错误:

#1054 - Unknown column 'user.user_id' in 'where clause' 

如何在子查询的 WHERE 条件中使用 user.user_id 值？

谢谢。

Answer 1

试试这个方法:

SELECT *,
       COALESCE((SELECT SUM(user_points.points) 
                 FROM user_points
                 WHERE user_points.user_id = user.user_id), 0)
        +
        COALESCE((SELECT SUM(used_points.points)  
                  FROM used_points
                  WHERE used_points.user_id = user.user_id), 0) AS total_points_credit
FROM user