SQL查询咨询：统计高速公路与桥梁数量相等的州的总数

问题背景 #

现有三张表：

• state表：存储州名，code为各州唯一主键
• highways表：存储州内高速公路信息，code关联state表主键
• bridges表：存储州内桥梁信息，code关联state表主键

需求是统计高速公路数量与桥梁数量相等的州的总数。原查询尝试通过子查询统计每个州的高速和桥梁数量，但外层count(*)未筛选符合条件的州，且添加WHERE子句会触发can't group by错误，需修正查询逻辑。

原错误查询语句：

SELECT count(*)
from
    (SELECT count(h.name), count(b.name)
    FROM state c
    INNER JOIN highways h
    on c.code = l.code  -- 拼写错误：l应为h
    INNER JOIN bridge b  -- 表名错误：应为bridges
    on c.code = g.code  -- 拼写错误：g应为b
    Group By c.code
    );

示例数据 #

state表 #

highways表 #

bridges表 #

修正后的查询方案 #

核心问题说明 #

原查询直接关联highways和bridges会产生笛卡尔积，导致统计数量失真（比如OH的3条高速和2座桥会关联出6条记录，count(h.name)会得到6而非3），同时存在表名/别名拼写错误。

正确查询语句 #

SELECT COUNT(*) AS matching_state_count
FROM (
    SELECT 
        s.code,
        COALESCE(h.highway_count, 0) AS highway_count,
        COALESCE(b.bridge_count, 0) AS bridge_count
    FROM state s
    LEFT JOIN (
        -- 单独统计每个州的高速公路数量
        SELECT code, COUNT(name) AS highway_count
        FROM highways
        GROUP BY code
    ) h ON s.code = h.code
    LEFT JOIN (
        -- 单独统计每个州的桥梁数量
        SELECT code, COUNT(name) AS bridge_count
        FROM bridges
        GROUP BY code
    ) b ON s.code = b.code
    -- 筛选高速与桥梁数量相等的州
    WHERE COALESCE(h.highway_count, 0) = COALESCE(b.bridge_count, 0)
) AS state_counts

逻辑解释 #

1. 子查询单独统计：分别对highways和bridges按code分组统计数量，避免笛卡尔积导致的错误统计
2. LEFT JOIN + COALESCE：确保没有高速/桥梁的州也能被纳入统计，用COALESCE把NULL值转为0，避免比较时出错
3. 外层筛选统计：在内层结果中筛选出数量相等的州，最后用COUNT(*)统计符合条件的州总数

内容的提问来源于stack exchange，提问作者Vin rich