mysql - 从 SQL (MySQL) 中的两个不同表中减去计算值
问题描述
在我的程序中,用户可以通过在该地点签到来赚取积分。
然后,他们可以使用这些积分在该地点兑换奖励。积分不可跨场馆共享。
我有以下表格:
用户
- ID
- 姓名
场地
- ID
- 姓名
签到
- ID
- user_id (FK):获得积分的用户
- 场地_id(FK):用户在其中获得积分的场地
- 积分:获得的积分数量
奖励
- ID
- 姓名
- 场地_id:奖励所属的场地
- 积分:奖励成本的金额
奖励兑换
- ID
- 用户身份
- 奖励ID
我可以按如下方式计算用户的总积分:
select venue_id, sum(points) as total_points
from check_ins
WHERE user_id = 109
group by venue_id, user_id;
我可以像这样计算场地兑换的积分数:
select venue_id, sum(points) as points_redeemed
from reward_redemptions
JOIN rewards on reward_redemptions.reward_id = rewards.id
GROUP BY venue_id;
然后我可以使用编程语言(例如 PHP)通过减去它们来计算每个场地的剩余分数。
但是,我想使用 SQL 来执行此操作(理想情况下在 1 个查询中,以减少查询之间更改数据的机会,使代码更简单,并可能提高性能)。理想的最终结果是这样的:
venue_id total_points points_redeemed points_remaining
#5 10 2 8
#15 25 10 15
#189 150 20 130
所以我的问题是:
- 是否可以在一次查询中获得全部积分和兑换积分?
- 是否可以一次查询获得总积分、兑换积分和剩余积分?
解决方案
您可以使用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;
通过减去总和可以很容易地计算出差值。
推荐阅读
- google-apps-script - 尝试使用服务帐户从 GCS 获取对象时出现 404
- numpy - 将 scipy 过滤器应用于多维数据
- python - 需要在 Python 中的 Pareto 分布代码中进行说明
- javascript - 如何将一个新元素添加到另一个 JS 中
- javascript - 更智能的 JQuery 选择器?
- python - 带有 9 个不同符号的进度条
- python - 尝试连接到 CosmosDB 数据库时出现 pymongo.errors.ServerSelectionTimeoutError
- android - 使用 Prism 重新打开页面时 Xamarin.Forms 应用程序错误
- python - GroupBy 索引和列然后转换 Pandas 中的选定列
- java - NetBeans 8.2 无法“介入”