从SQL(MySQL)中的两个不同表中减去计算值

你可以用 union all group by . 以下是关于场馆和积分的查询:

select venue_id, sum(total_points) as total_points,
       sum(points_redeemed) as points_redeemed
from ((select venue_id, sum(points) as total_points, 0 as points_redeemed
       from check_ins
       group by venue_id
      )
      union all
      (select venue_id, 0 as total_points, sum(points) as points_redeemed
       from reward_redemptions rr join
            rewards r
            on rr.reward_id = r.id
       group by venue_id
      )
     ) p
group by venue_id;

通过减去总和,可以很容易地计算出差值。

SELECT c.venue_id, c.user_id
        SUM(c.points) AS total_points, 
        IFNULL(r.points_redeemed, 0) AS points_redeemed, 
        SUM(c.points) - IFNULL(r.points_redeemed, 0) AS points_remaining
FROM check_ins AS c
LEFT JOIN (
    select venue_id, user_id, sum(points) as points_redeemed
    from reward_redemptions
    JOIN rewards on reward_redemptions.reward_id = rewards.id
    GROUP BY venue_id, user_id) AS r
ON c.venue_id = r.venue_id AND c.user_id = r.user_id
GROUP BY c.venue_id, c.user_id

左联接对于包含尚未兑换任何东西的用户是必需的。