MySQL右外连接空值，所有用户的排名(排名)

Question

我正在尝试创建一个排名(排名)查询。

我使用表users、schedule 和picks来计算排名。然而，这样做的缺陷是，如果用户未提交任何选择，则该用户将不会出现在排名中。

下面的查询返回所有已提交选择的用户。用户 2 和用户 4 尚未提交选择，因此未显示。

+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
| userID | userName | totalWins | totalLost | totalPushs | totalPoints | totalBets | trueBets |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   1    |   aaaa   |     0     |     0     |     1      |     0.5     |     1     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   3    |   cccc   |     0     |     0     |     1      |     0.5     |     1     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   5    |   eeee   |     0     |     0     |     1      |     0.5     |     1     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+

我正在尝试创建一个所有用户都会出现在排行榜中的项目，无论他们是否提交了选择。就像下面这样。

+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
| userID | userName | totalWins | totalLost | totalPushs | totalPoints | totalBets | trueBets |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   1    |   aaaa   |     0     |     0     |     1      |     0.5     |     1     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   2    |   bbbb   |     0     |     0     |     0      |      0      |     0     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   3    |   cccc   |     0     |     0     |     1      |     0.5     |     1     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   4    |   dddd   |     0     |     0     |     0      |      0      |     0     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   5    |   eeee   |     0     |     0     |     1      |     0.5     |     1     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+

但是，我已将其退回

+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
| userID | userName | totalWins | totalLost | totalPushs | totalPoints | totalBets | trueBets |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   1    |   aaaa   |     0     |     0     |     1      |     0.5     |     1     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|  NULL  |   NULL   |     0     |     0     |     0      |      0      |     0     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   3    |   cccc   |     0     |     0     |     1      |     0.5     |     1     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|  NULL  |   NULL   |     0     |     0     |     0      |      0      |     0     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
|   5    |   eeee   |     0     |     0     |     1      |     0.5     |     1     |    0     |
+--------+----------+-----------+-----------+------------+-------------+-----------+----------+

我已经到了这样的地步:我正在尝试右外连接 users 表，其中所有用户都驻留在查询中，但由于没有匹配项，NULL 值按预期显示。

如何用正确的 userID 和 userName 替换这些 NULL 值

这是我的代码

SELECT
    lt.userID,
    lt.userName,
    IFNULL(lt.totalWins,0) AS totalWins,
    IFNULL(lt.totalLost,0) AS totalLost,
    IFNULL(lt.totalPushs,0) AS totalPushs,
    IFNULL((totalWins+(totalPushs*.5)),0) AS totalPoints,
    IFNULL(totalWins+totalLost+totalPushs,0) AS totalBets,
    IFNULL(totalWins+totalLost,0) AS trueBets
FROM (
    SELECT
        userID,
        userName,
        SUM(win) AS totalWins,
        SUM(lost) AS totalLost,
        SUM(push) AS totalPushs
    FROM (
        SELECT
            *,
            (finalResult = 'win') AS win,
            (finalResult = 'loss') AS lost,
            (finalResult = 'push') AS push
        FROM (
            SELECT 
                users.userID,
                userName,
                IF (pickID=visitorID, visitorResult, homeResult) AS finalResult
            FROM
                users
            JOIN
                picks 
            ON
                users.userID = picks.userID
            JOIN
                schedule
            ON
                picks.gameID = schedule.gameID
            WHERE
                weekNum <= 1
        ) x
    ) x
    GROUP BY
        userID
) lt
RIGHT OUTER JOIN
    users
ON
    users.userID = lt.userID
LIMIT 100

Answer 1

你可以尝试:

SELECT
    lt.userID,
    lt.userName,
    IFNULL(lt.totalWins,0) AS totalWins,
    IFNULL(lt.totalLost,0) AS totalLost,
    IFNULL(lt.totalPushs,0) AS totalPushs,
    IFNULL((totalWins+(totalPushs*.5)),0) AS totalPoints,
    IFNULL(totalWins+totalLost+totalPushs,0) AS totalBets,
    IFNULL(totalWins+totalLost,0) AS trueBets
FROM users
RIGHT OUTER JOIN
    (
    SELECT
        userID,
        userName,
        SUM(win) AS totalWins,
        SUM(lost) AS totalLost,
        SUM(push) AS totalPushs
    FROM (
        SELECT
            *,
            (finalResult = 'win') AS win,
            (finalResult = 'loss') AS lost,
            (finalResult = 'push') AS push
        FROM (
            SELECT 
                users.userID,
                userName,
                IF (pickID=visitorID, visitorResult, homeResult) AS finalResult
            FROM
                users
            JOIN
                picks 
            ON
                users.userID = picks.userID
            JOIN
                schedule
            ON
                picks.gameID = schedule.gameID
            WHERE
                weekNum <= 1
        ) x
    ) x
    GROUP BY
        userID
) lt
ON
    users.userID = lt.userID
LIMIT 100

我还没有对此进行测试，但主要的更改是从 users 表开始，然后加入您的结果。您之前的问题是因为您从结果开始并尝试加入用户，并且当用户未进行任何选择时，它们将返回 null。这种方式应该会导致结果为空，但会显示所有用户，因为您的起点就在那里，因此您将提取所有记录