mysql - 为什么我的 IfNull() 函数不起作用？

Question

SELECT
    f.province AS loc,
    IFNULL(COUNT(f.province), 0) AS count
FROM
    project_name_data d
RIGHT JOIN full_province f ON (
    TRIM(
        REPLACE (
            REPLACE (
                SUBSTRING_INDEX(d.location, '|', 1),
                '省',
                ''
            ),
            '壮族',
            ''
        )
    ) = f.province
)
GROUP BY
    loc;

这是我查询的Sql，full_province是一张包含中国所有省份的表，project_name_data是指主要数据。现在我想计算每个省有多少记录，对于没有任何记录的省，给它们0。但是我的Sql只返回包含记录的省份。有没有错误？

举个不恰当的例子:

-- main table
id   location      comment  other columns
1    Los Angeles   aaa        ...
2    New York      bbb        ...
3    Cambridge     ccc        ...
4    Philadelphia  ddd        ...
5    New York      eee        ...
6    Cambridge     fff        ...

--full_province table
id    location
1     Los Angeles
2     New York
3     Cambridge
4     Philadelphia
5     Beijing
6     Tokyo
7     Barcelona
8     Paris
9     Toronto

期望输出:

      location      count
1     Los Angeles     1
2     New York        2
3     Cambridge       2
4     Philadelphia    1
5     Beijing         0
6     Tokyo           0
7     Barcelona       0
8     Paris           0
9     Toronto         0

Answer 1

IFNULL 可以删除，因为如果 d.province 值为 NULL COUNT 将不会累加。将返回 0。

SELECT
    f.province AS loc,
    COUNT(d.province) AS count
FROM
    project_name_data d
RIHGT JOIN full_province f ON (
    TRIM(
        REPLACE (
            REPLACE (
                SUBSTRING_INDEX(d.location, '|', 1),
                '省',
                ''
            ),
            '壮族',
            ''
        )
    ) = f.province
)
GROUP BY f.province;

编辑

我看到你添加了一些示例数据。

你可以试试这个查询。

SELECT
    f.location AS loc,
    COUNT(d.location) AS CNT
FROM
    project_name_data d
right JOIN full_province f
ON d.location= f.location
GROUP BY f.location
ORDER BY d.id desc

sqlfiddle

[结果]:

|          loc | CNT |
|--------------|-----|
| Philadelphia |   1 |
|    Cambridge |   2 |
|     New York |   2 |
|  Los Angeles |   1 |
|      Beijing |   0 |
|    Barcelona |   0 |
|      Toronto |   0 |
|        Tokyo |   0 |
|        Paris |   0 |