如何去除SQL查询中列值互换产生的近似重复镜像行

核心思路是把库标识+文件ID作为全局唯一的文件判定维度，通过固定两组唯一值的大小关系消除镜像行，完全兼容动态过滤条件和跨库查询场景。

具体修改逻辑 #

把原查询WHERE条件中的A.file_id <> B.file_id替换为以下判断逻辑即可：

-- 先比较库标识，库不同时直接按库标识大小排序；库相同时按文件ID排序
(org_db < fnd_db) OR (org_db = fnd_db AND A.file_id < B.file_id)

如果你的SQL方言不支持直接在WHERE里引用SELECT里定义的别名，就直接写实际值：

-- 同库查询的场景（你当前示例的场景）
(1 = 1 AND A.file_id < B.file_id)

-- 跨库查询的场景（比如A查org_db=1的库，B查fnd_db=2的库）
(1 < 2) OR (1 = 2 AND A.file_id < B.file_id)
-- 跨库时库标识大小是固定的，所以后面的A.file_id比较自动不生效，不会出现跨库file_id冲突的问题

优化后完整SQL示例（同库场景） #

同时修正了你原查询中隐式连接+显式连接混写、末尾多余AND的语法问题：

SELECT
    A.file_id org_file_id,
    B.file_id fnd_file_id,
    AF.directory_id org_dir_id,
    BF.directory_id fnd_dir_id,
    AD.disk_id org_disk,
    BD.disk_id fnd_disk,
    1 org_db,
    1 fnd_db
FROM fhash A
INNER JOIN fhash B ON B.data = A.data
INNER JOIN file AF ON A.file_id = AF._id
INNER JOIN file BF ON B.file_id = BF._id
INNER JOIN directory AD ON AF.directory_id = AD._id
INNER JOIN directory BD ON BF.directory_id = BD._id
WHERE
    -- 替换原来的A.file_id <> B.file_id
    (1 = 1 AND A.file_id < B.file_id) AND
    A.file_id IN (SELECT _id FROM file WHERE directory_id IN (SELECT _id FROM directory WHERE disk_id <> 0)) AND
    B.file_id IN (SELECT _id FROM file WHERE directory_id IN (SELECT _id FROM directory WHERE disk_id <> 0))
ORDER BY org_file_id

兼容性说明 #

• 完全适配你提到的动态过滤条件：新增的大小判断条件和原有过滤逻辑完全独立，不会互相影响
• 跨库场景直接适配：只要修改org_db和fnd_db的固定值，逻辑自动生效，不会出现两个库file_id重复导致的误判问题

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