MySQL 总和 + 总和

Question

我基本上有三个表:

hunt_c_usershunt_c_collected_eggshunt_c_achievements

我目前只使用 hunt_c_users 和 hunt_c_collected_eggs 上的 LEFT JOIN 获取排行榜，但现在我还需要包括 hunt_c_achievements 点数。 '

目前我的查询是:

SELECT
    hunt_c_users.id AS 'id',
    CONCAT(first_name, ' ', LEFT(last_name,1), '. From ', city) AS 'user_byline',
    fbid AS 'user_fbid',
    SUM(hunt_c_collected_eggs.value) AS user_points
    FROM hunt_c_users
    LEFT JOIN hunt_c_collected_eggs ON hunt_c_users.id = hunt_c_collected_eggs.user_id
    WHERE hunt_c_users.id NOT IN ($bannedIDSstr)
    GROUP BY hunt_c_users.id
    HAVING SUM(hunt_c_collected_eggs.value) > 0
    ORDER BY user_points DESC
    LIMIT 10

但是当我在 hunt_c_achievements 和另一个 SUM(hunt_c_achievements.value) 上添加另一个 LEFT JOIN 时，user_points 非常不准确，我明白为什么会这样(表格是水平连接的，因此成就会添加到每个项目上)，但是我不太明白如何解决它。

这是给我不准确结果的查询:

SELECT
    hunt_c_users.id AS 'id',
    CONCAT(first_name, ' ', LEFT(last_name,1), '. From ', city) AS 'user_byline',
    fbid AS 'user_fbid',
    SUM(hunt_c_collected_eggs.value) + SUM(hunt_c_achievements.value) AS user_points
    FROM hunt_c_users
    LEFT JOIN hunt_c_collected_eggs ON hunt_c_users.id = hunt_c_collected_eggs.user_id
    LEFT JOIN hunt_c_achievements ON hunt_c_users.id = hunt_c_achievements.user_id
    WHERE hunt_c_users.id NOT IN ($bannedIDSstr)
    GROUP BY hunt_c_users.id
    HAVING SUM(hunt_c_collected_eggs.value) > 0
    ORDER BY user_points DESC
    LIMIT 10

---

更新

我在这里添加了一个 SQL Fiddle:http://sqlfiddle.com/#!2/ac1f69/1

我使用提供的虚拟数据的预期结果是:

id   | user_byline | user_fbid | user_points
------------------------------------------
1033 | ...         | ...       | 111
1030 | ...         | ...       | 97
1031 | ...         | ...       | 62
1032 | ...         | ...       | 27

Answer 1

您必须使用子查询来单独计算总和:

SET @bannedIDSstr = 0;

SELECT
    user.id AS 'id',
    CONCAT(user.first_name, ' ', LEFT(user.last_name, 1), '. From ', user.city) AS 'user_byline',
    user.fbid AS 'user_fbid',
    IFNULL(eggs_sum.collected_eggs, 0) + IFNULL(achievement_sum.achievement, 0) AS user_points
    FROM hunt_c_users AS user
    LEFT JOIN
    (SELECT
        eggs.user_id,
        SUM(eggs.value) AS collected_eggs
        FROM hunt_c_collected_eggs AS eggs
        GROUP BY eggs.user_id
        HAVING collected_eggs > 0
    ) AS eggs_sum
    ON user.id = eggs_sum.user_id
    LEFT JOIN
    (SELECT
        achievements.user_id,
        SUM(achievements.value) AS achievement
        FROM hunt_c_achievements AS achievements
        GROUP BY achievements.user_id
    ) AS achievement_sum
    ON user.id = achievement_sum.user_id
    WHERE user.id NOT IN (@bannedIDSstr)
    GROUP BY user.id
    ORDER BY user_points DESC
    LIMIT 10;

演示@ SQL Fiddle