MySQL - 从两个不同的表中减去总和

Question

我想要连接表points_minus并执行类似(SUM(p.points) - SUM(m.points)) AS point的操作，而不是这样做return $points - $this->getMUserPoints($user_id); 在我看来，这有点多了。如果可以在不使用子查询的情况下完成，那就太好了。

    /**
    * Get plus user points.
    *
    * @param (int) $user_id
    */
    public function getUserPoints($user_id) {
        if($q = $this->db->mysqli->prepare("SELECT SUM(points) FROM points_plus WHERE user_id = ?"))
        {
            $q->bind_param("i", $user_id);
            $q->execute();

            $q->bind_result($points);

            $q->fetch();
            $q->close();
            return $points - $this->getMUserPoints($user_id);
        }
            return false;
    }


    /**
    * Get minus user points.
    *
    * @param (int) $user_id
    */
    public function getMUserPoints($user_id) {
        if($q = $this->db->mysqli->prepare("SELECT SUM(points) FROM points_minus WHERE user_id = ?"))
        {
            $q->bind_param("i", $user_id);
            $q->execute();

            $q->bind_result($points);

            $q->fetch();
            $q->close();
            return $points;
        }
            return false;
    }

到目前为止...但它返回一个负数:

"SELECT (SUM(p.points) - SUM(m.points) )
FROM points_plus p
LEFT JOIN
points_minus m 
ON p.user_id = m.user_id
WHERE p.user_id = ?
GROUP BY p.user_id"))

Answer 1

尝试分开:

select sum(points) 
from (select points from points_plus where user_id = ?
      union all
      select -points from points_minus where user_id = ?) t

另一个版本，但我不确定这是否是 MySql 的正确语法:

select (select sum(points) from points_plus where user_id = ?) -
       (select sum(points) from points_minus where user_id = ?)