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_provice是一个包含中国所有省份的表,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
IFNULL
可以删除,因为如果d.province
的值为NULL
,则COUNT
将不会累积。将返回CCD_ 5。
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 |
因为COUNT,它永远不会返回值NULL。
我将替换sum(case when f.province is not null then 1 else 0 end)
替换:
IFNULL(COUNT(f.province), 0) AS count
带有:
sum(case when d.location is not null then 1 else 0 end)
可能会更好——然后计算主表中链接到省表的非空位置的数量,如果(由于正确的联接(位置为空,则为零。