如何查询两张表中同provinceid下cityid完全匹配的记录

你原SQL存在笔误：on t1=provinceid应为t1.provinceid，且原逻辑用两次左连接加子查询，大表下性能较低，以下是更优的实现方案：

方案1：通用高效写法（兼容所有主流SQL数据库） #

该方案仅需少量表扫描，大数据量下性能优势明显：

-- 先筛选出城市集合完全匹配的省份ID
WITH matched_provinces AS (
    SELECT 
        t1.provinceid
    FROM t1
    JOIN t2 ON t1.provinceid = t2.provinceid AND t1.cityid = t2.cityid
    GROUP BY t1.provinceid
    HAVING COUNT(*) = (SELECT COUNT(*) FROM t1 t1_sub WHERE t1_sub.provinceid = t1.provinceid)
    AND COUNT(*) = (SELECT COUNT(*) FROM t2 t2_sub WHERE t2_sub.provinceid = t1.provinceid)
)
-- 取出对应省份的所有城市ID
SELECT t1.provinceid, t1.cityid 
FROM t1
JOIN matched_provinces mp ON t1.provinceid = mp.provinceid
ORDER BY t1.provinceid, t1.cityid;

逻辑说明：通过三个指标判断集合一致：①两张表同一省份下的城市总数相等、②两张表能匹配上的城市数等于总数，即可证明两个城市集合完全重合。

方案2：聚合函数简便写法（兼容MySQL/PostgreSQL/SQL Server等支持字符串聚合的数据库） #

代码更简洁，可读性更高，适合中小数据量场景：

-- MySQL版本示例，其他数据库替换GROUP_CONCAT为对应聚合函数即可（PG用STRING_AGG、Oracle用LISTAGG）
WITH t1_agg AS (
    SELECT provinceid, GROUP_CONCAT(cityid ORDER BY cityid) AS city_set
    FROM t1
    GROUP BY provinceid
),
t2_agg AS (
    SELECT provinceid, GROUP_CONCAT(cityid ORDER BY cityid) AS city_set
    FROM t2
    GROUP BY provinceid
)
SELECT t1.provinceid, t1.cityid
FROM t1
JOIN t1_agg a1 ON t1.provinceid = a1.provinceid
JOIN t2_agg a2 ON a1.provinceid = a2.provinceid AND a1.city_set = a2.city_set
ORDER BY t1.provinceid, t1.cityid;

注意：聚合时必须加ORDER BY cityid保证字符串顺序一致，避免同集合不同顺序导致对比失败。

内容的提问来源于stack exchange，提问作者s ab