如何从包含引用同一列的两个外键的 SQLite 表中获取信息

Question

作为个人项目的一部分，我构建了一个数据库，其中包含一个文本文件表(它们的路径和基名作为列(，以及另一个表，该表指示哪些文件是其他文件的副本。

这是第一个表的外观：

files(
filesid INTEGER PRIMARY KEY UNIQUE,
filename TEXT,
filesource TEXT,
path TEXT UNIQUE)

这是第二个：

duplicatefiles(
id INTEGER PRIMARY KEY UNIQUE,
filesid INTEGER,
duplicateof INTEGER,
songid INTEGER,
FOREIGN KEY(filesid) REFERENCES lyricsfiles(filesid),
FOREIGN KEY(duplicateof) REFERENCES lyricsfiles(filesid),

最后一个表有两列外键引用文件表中的 ID，指示这两个文件彼此重复。

我这样做是为了尽量减少信息重复，但现在意识到我不知道如何查询此表以获取我真正想要的内容，即两个文件的路径。 我已经阅读了有关别名的信息，但是在这种情况下我找不到有关如何使用它们的文档。

我正在使用sqlite3和python3。

所以我的问题是：查询数据库的正确方法是什么，因为它是获取我需要的信息？在数据库中表示这些信息的更好方法是什么？

Answer 1

您需要加入您的表格files2 次。为此，您需要表别名：

SELECT
ORI.filesid original_filesid
,ORI.filename original_filename
,ORI.filesource original_filesource
,DUP.filesid duplicate_filesid
,DUP.filename duplicate_filename
,DUP.filesource duplicate_filesource
FROM duplicatefiles DPF
JOIN files ORI
ON ORI.filesid = DPF.duplicateof
JOIN files DUP
ON DUP.filesid = DPF.filesid

您会看到我以别名 ORI 的身份加入files，表明此别名代表原始文件(另请参阅连接条件ORI.filesid = DPF.duplicateof(。重复项通过表别名 DUP 连接。然后，使用别名选择所需的字段，还可以重命名它们以更好地理解ORI.filesid重命名为original_filesid。