如何摆脱实际上"duplicates"几乎等同的调用行，实际上只切换了部分列数据？

Question

我在保存文件信息的数据库上工作。数据分布在几个表中，File表中的数据包含file_id，在disk、directory、hashes等其他表中有相关数据。我目前的目标是能够根据收集的早期文件哈希数据找到重复的文件。另外，目标是能够一次跨两个数据库进行查询，因此查询结果也将包括指示源数据库的值，在此查询中，这些值是虚拟的，并且作为org_db和fnd_db作为占位符出现在结果中，正如我猜想的那样，它们的存在对于解决我的问题很重要，所以我确实提到了它们的存在。

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, file AF, file BF, directory AD, directory BD
INNER JOIN fhash B ON B.data = A.data
WHERE
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)) AND
A.file_id = AF._id AND
B.file_id = BF._id AND
AF.directory_id = AD._id AND
BF.directory_id = BD._id AND
ORDER BY org_file_id

| org_file_id | fnd_file_id | org_dir_id | fnd_dir_id | org_disk | fnd_disk | org_db | fnd_db |
===============================================================================================
| 97          | 118         | 5          | 8          | 2        | 3        | 1      | 1      |
| 106         | 147         | 8          | 9          | 3        | 3        | 1      | 1      |
| 106         | 175         | 8          | 10         | 3        | 3        | 1      | 1      |
| 107         | 148         | 8          | 9          | 3        | 3        | 1      | 1      |
| 107         | 176         | 8          | 10         | 3        | 3        | 1      | 1      |
| 108         | 149         | 8          | 9          | 3        | 3        | 1      | 1      |
| 108         | 177         | 8          | 10         | 3        | 3        | 1      | 1      |
| 110         | 151         | 8          | 9          | 3        | 3        | 1      | 1      |
| 110         | 179         | 8          | 10         | 3        | 3        | 1      | 1      |
...
| 118         | 97          | 8          | 5          | 3        | 2        | 1      | 1      |
| 147         | 106         | 9          | 8          | 3        | 3        | 1      | 1      |
| 148         | 107         | 9          | 8          | 3        | 3        | 1      | 1      |
| 149         | 108         | 9          | 8          | 3        | 3        | 1      | 1      |
| 151         | 110         | 9          | 8          | 3        | 3        | 1      | 1      |
| 175         | 106         | 10         | 8          | 3        | 3        | 1      | 1      |
| 176         | 107         | 10         | 8          | 3        | 3        | 1      | 1      |
| 177         | 108         | 10         | 8          | 3        | 3        | 1      | 1      |
| 179         | 110         | 10         | 8          | 3        | 3        | 1      | 1      |

我得到的结果是相当不错的，可以在进一步处理后使用，但我得到的是我想要的两倍。在表中，每一行都有一个镜像行，实际上，下面四对列中的每一对都是前一行的副本，数据在对应的列之间切换。

我想知道的是，如果，以及如何从查询中直接得到正确的结果，而不需要进一步处理。

如果我只有两列，没有磁盘、目录和数据库等额外因素，那么修改

就很简单了。

A.file_id <> B.file_id 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)) 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)) AND

或

A.file_id IN (SELECT _id FROM file WHERE directory_id IN ([query gets ids of dir  subdirs])) AND
B.file_id IN (SELECT _id FROM file WHERE directory_id IN (SELECT _id FROM directory WHERE disk_id <> 0)) AND

或者以其他方式。除了进一步修改以使其跨两个数据库工作外，查询的其余部分基本保持不变。

Answer 1

对于这个示例数据，我认为使用:

GROUP BY MIN(org_file_id, fnd_file_id), MAX(org_file_id, fnd_file_id)

，其中MIN()和MAX()是SQLite的标量函数，将消除重复:

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 file AF ON A.file_id = AF._id
INNER JOIN directory AD ON AF.directory_id = AD._id
INNER JOIN fhash B ON B.data = A.data
INNER JOIN file BF ON B.file_id = BF._id
INNER JOIN directory BD ON BF.directory_id = BD._id
WHERE 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))
GROUP BY MIN(org_file_id, fnd_file_id), MAX(org_file_id, fnd_file_id)
ORDER BY org_file_id;

我还更改了ON

子句的正确显式连接。查看一个简化的演示。