mysql - sql查询将两个查询合并为一个空行

Question

这是我的sql表结构:

表 1:详细信息

    |--id--|--id_user--|--price--|
    |  1   |    1      |   10    |
    |  2   |    2      |   15    |
    |  3   |    1      |   25    |
    |  4   |    3      |   30    |
    |  5   |    3      |   7     |
    ------------------------------

表2:用户

    |--id--|--id_country--|
    |  1   |       1      |
    |  2   |       2      |
    |  3   |       0      |
    -----------------------

表 3:国家

    |--id--|--country--|
    |  1   |  France   |
    |  2   |  Italy    |
    --------------------

我需要的是按国家/地区获取价格总和:

SELECT c.country, SUM(d.price) AS price
    FROM details d
        INNER JOIN users u ON u.id = d.id_user
        INNER JOIN country c ON  c.id = u.id_country
    GROUP BY c.country
    ORDER BY c.country

我明白了:

|--country--|--price--|
|  France   |   35    |
|  Italy    |   15    |
-----------------------

但我需要得到这个:

|--country--|--price--|
|  France   |   35    |
|  Italy    |   15    |
| Undefined |   37    |
-----------------------

如果 id_country=0，则 undefined 会出现。 (我无法将 id=0 或 id=undefined 添加到国家/地区表，它会搞乱其他内容)。现在我通过两个单独的查询来实现这一点，第二个是:

SELECT SUM(d.price) as price
    FROM details d 
        INNER JOIN users u ON u.id = d.id_user AND u.id_country=0
    GROUP BY u.id_country

我在想是否...是否可以在一个查询中执行此操作？

Answer 1

在这种情况下你需要使用左连接:

SELECT c.country, SUM(d.price) AS price
    FROM details d
        LEFT JOIN users u ON u.id = d.id_user
        LEFT JOIN country c ON  c.id = u.id_country
    GROUP BY c.country
    ORDER BY c.country

如果您使用 INNER JOIN，您将只会得到同时存在于两个表中的结果。

将 NULL 替换为 Undefined 使用:

SELECT IFNULL(c.country,'Undefined') AS Country, SUM(d.price) AS price
    FROM details d
        LEFT JOIN users u ON u.id = d.id_user
        LEFT JOIN country c ON  c.id = u.id_country
    GROUP BY c.country
    ORDER BY c.country

一种将 Undefined 排序到最后的方法是添加一个 Sortfield

SELECT A.Country,A.Price FROM (
    SELECT IFNULL(c.country,'Undefined') AS Country, SUM(d.price) AS price, IFNULL(c.Country,'ZZZZZZZZ') AS Sort
        FROM details d
            LEFT JOIN users u ON u.id = d.id_user
            LEFT JOIN country c ON  c.id = u.id_country
        GROUP BY c.country
) A
ORDER BY A.Sort

编辑:评论中建议的 ORDER BY

SELECT IFNULL(c.country,'Undefined') AS Country, SUM(d.price) AS price
    FROM details d
        LEFT JOIN users u ON u.id = d.id_user
        LEFT JOIN country c ON  c.id = u.id_country
    GROUP BY c.country
    ORDER BY c.country IS NULL, c.country