选择表A中的记录，*仅*表B中的某些对应记录 [英] Select records in table A with *only* certain corresponding records in table B

问题描述

例如，假设您有一个文件表和一个传输操作的日志表.

For example, say you have a table of files and a logging table of transfer operations.

CREATE TABLE files
(
  id INTEGER PRIMARY KEY
  -- various other columns
);

CREATE TABLE transfers
(
  id      INTEGER PRIMARY KEY,
  file_id INTEGER,
  status  TEXT NOT NULL,
  -- various other columns
  FOREIGN KEY (file_id) REFERENCES files (id)
)

一个传输操作可以有多种状态——'succeeded'、'failed'、'in progress'等.一个文件可以有许多传输操作 -- 特别是，如果传输失败，则可以稍后安排该文件的另一次传输.

A transfer operation can have various statuses -- 'succeeded', 'failed', 'in progress', etc. One file can have many transfer operations -- in particular, if a transfer fails, another transfer for that file may be scheduled later.

现在，假设我们要查找只有传输失败的所有文件——当前没有正在进行的传输，以后也没有成功的传输.

Now, say we want to find all the files with only failed transfers -- no transfers currently in progress, no later successful transfers.

到目前为止，我有以下带有子选择的解决方案:

So far I have the solution below with subselects:

SELECT files.*
FROM files
WHERE files.id IN (
  SELECT DISTINCT file_id
  FROM transfers
  WHERE transfers.status == 'failed'
) AND files.id NOT IN (
  SELECT DISTINCT file_id
  FROM transfers
  WHERE transfers.status <> 'failed'
)

但是，这感觉有点笨拙和程序化.是否有更优雅的解决方案，可能涉及自联接?

However, this feels a little clunky and procedural. Is there a more elegant solution, possibly involving self-joins?

推荐答案

如果你想使用自联接:

SELECT DISTINCT files.*
FROM
  files INNER JOIN transfers t1
  ON files.id = t1.file_id AND t1.status='failed'
  LEFT JOIN transfers t2
  ON file.id = t2.file_id AND t2.status<>'failed'
WHERE
  t2.id IS NULL

这篇关于选择表A中的记录，*仅*表B中的某些对应记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！