QGIS SQLite环境统计重复GID_New并列出所属表名的SQL实现方案

可直接运行的SQL实现 #

SELECT 
    GID_New,
    GROUP_CONCAT(DISTINCT TableName, ', ') AS "Table Name",
    COUNT(*) AS no_matches
FROM (
    SELECT GID_New, 'Drainage_Lines' AS TableName FROM "Drainage_Lines"
    UNION ALL
    SELECT GID_New, 'Drainage_Points' AS TableName FROM "Drainage_Points"
    UNION ALL
    SELECT GID_New, 'Drainage_Gross_Pollutant_Traps' AS TableName FROM "Drainage_Gross_Pollutant_Traps"
) t
GROUP BY GID_New
HAVING COUNT(*) > 1

核心修改说明 #

1. 子查询中每个UNION分支都新增了硬编码的TableName字段，明确记录每条数据的来源表
2. 外层聚合使用SQLite原生的GROUP_CONCAT函数拼接同一GID_New对应的所有表名，添加DISTINCT关键字避免同一张表内存在多条重复ID时，表名重复出现
3. 给GROUP_CONCAT指定第二个参数为, ，实现表名之间用逗号+空格分隔，完全匹配你需要的输出格式

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