mysql - 用总和与三个表连接

我有三张桌子

[使用者]

id
name
email

[交易]

id
user_id
amount
points_rewarded

[兑换]

id
user_id
points_redeemed

我想查询数据库以获取以下字段

users.id
users.name
transactions   //   Number of transactions
redemptions    //   Number of redemptions
points         //   Sum of points_rewarded - sum of points_redeemed

我可以通过事务或兑换的方式加入用户表，但似乎无法将所有三个表合并到一个查询中。

[用户+交易]

SELECT users.id,users.name,COUNT(transactions.id) AS transactions,SUM(transactions.points_rewarded) AS points_rewarded FROM users LEFT JOIN transactions ON users.id=transactions.user_id GROUP BY users.id, users.name

可以为users.id，users.name，transactions和points_rewarded提供正确的值。

[用户+兑换]

SELECT users.id,users.name,COUNT(redemptions.id) AS redemptions, IFNULL(SUM(redemptions.points_redeemed),0) AS points_redeemed FROM users LEFT JOIN redemptions ON users.id=redemptions.user_id GROUP BY users.id, users.name

可以为users.id，users.name，redemptions和points_redeemed提供正确的值。

如果我只是离开参加两个

SELECT users.id,users.name,COUNT(transactions.id) AS transactions,COUNT(redemptions.id) AS redemptions,SUM(transactions.points_rewarded) - IFNULL(SUM(redemptions.points_redeemed),0) AS points FROM users LEFT JOIN transactions ON users.id=transactions.user_id LEFT JOIN redemptions on users.id=redemptions.user_id GROUP BY users.id, users.name

兑换列的值错误。我基本上得到了所有points_rewarded值乘以可赎回的总数，使不可能的点（points_rewarded-points_redeemed）为负值。

任何帮助将是巨大的！

最佳答案

是的，因为左联接将返回记录的并集，这可能会改变总和，否则总和将独立地应用于每个集合（就像使用两个独立查询时的情况一样）

是的，这两个查询仍必须在“主”查询中共存：

SELECT users.id,
       users.name,
       t.transactions,
       r.redemptions,
       t.points_rewarded - r.points_redeemed AS points
FROM   users
       LEFT JOIN (
        SELECT
            users.id,
            COUNT(*) AS transactions,
            COALESCE(SUM(transactions.points_rewarded),0) AS points_rewarded
        FROM users LEFT JOIN transactions ON users.id=transactions.user_id
        GROUP BY users.id
       ) t ON users.id = t.user_id
       LEFT JOIN (
        SELECT
            users.id,
            COUNT(*) AS redemptions,
            COALESCE(SUM(redemptions.points_redeemed),0) AS points_redeemed
        FROM users LEFT JOIN redemptions ON users.id=redemptions.user_id
        GROUP BY users.id
       ) r ON users.id = r.user_id
;

关于mysql - 用总和与三个表连接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43366920/