如何刪除選擇到自身表中的對

Question

我自己加入了一個表，並且我在下圖中突出顯示了重複的對，如何刪除它們？

select DISTINCT A.name as name1 , B.name as name2
from (select name , ratings.* from reviewers inner join ratings on reviewers.id = 
ratings.reviewer_id ) A ,
(select name , ratings.* from reviewers inner join ratings on reviewers.id = 
ratings.reviewer_id ) B
where A.reviewer_id <> B.reviewer_id 
and A.book_id = B.book_id
order by name1 , name2 ASC

名稱1	名稱2
愛麗絲劉易斯	伊麗莎白·布萊克
克里斯·托馬斯	約翰史密斯
克里斯·托馬斯	麥克懷特
伊麗莎白·布萊克	愛麗絲劉易斯
伊麗莎白·布萊克	傑克格林
傑克格林	伊麗莎白·布萊克
喬馬丁內斯	麥克安德森
約翰史密斯	克里斯·托馬斯
麥克安德森	喬馬丁內斯
麥克懷特	克里斯·托馬斯

上表曾經是一張圖片

Answer 1

你可以這樣做

select Name1, Name2
from ...
where Name1 < Name2;

請參閱此範例

#

Answer 2

我已經建立了 DDL 和 DML 語句來重現資料庫並編寫檢索不重複對的查詢。這是可能對其他人有幫助的「建構」程式碼：

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100)
);

CREATE TABLE reviewers (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE ratings (
  id INT PRIMARY KEY,
  reviewer_id INT,
  book_id INT,
  rating INT,
  FOREIGN KEY (reviewer_id) REFERENCES reviewers(id),
  FOREIGN KEY (book_id) REFERENCES books(id)
);

-- Inserting sample records
INSERT INTO reviewers (id, name)
VALUES
  (1, 'Alice Lewis'),
  (2, 'Elizabeth Black'),
  (3, 'Chris Thomas'),
  (4, 'John Smith'),
  (5, 'Mike White'),
  (6, 'Jack Green'),
  (7, 'Joe Martinez'),
  (8, 'Mike Anderson');

INSERT INTO books (id, title)
VALUES
  (1, 'The Gulag Archipelago'),
  (2, 'One Day in the Life of Ivan Denisovich'),
  (3, 'Cancer Ward');

-- Insertion of rating records
INSERT INTO ratings (id, reviewer_id, book_id, rating)
VALUES
  (1, 1, 1, 4),
  (2, 1, 2, 3),
  (3, 2, 1, 5),
  (4, 2, 2, 4),
  (5, 2, 3, 2),
  (6, 3, 1, 3),
  (7, 3, 3, 4),
  (8, 4, 1, 2),
  (9, 4, 3, 3),
  (10, 5, 2, 5),
  (11, 6, 1, 1),
  (12, 6, 2, 3),
  (13, 6, 3, 4),
  (14, 7, 1, 3),
  (15, 7, 2, 4),
  (16, 8, 3, 2);

這是重構的查詢：

SELECT DISTINCT
  A.name AS name1,
  B.name AS name2
FROM
  (
    SELECT
      reviewers.id,
      reviewers.name,
      ratings.book_id
    FROM
      reviewers
      INNER JOIN ratings ON reviewers.id = ratings.reviewer_id
  ) A
  JOIN (
    SELECT
      reviewers.id,
      reviewers.name,
      ratings.book_id
    FROM
      reviewers
      INNER JOIN ratings ON reviewers.id = ratings.reviewer_id
  ) B ON A.book_id = B.book_id
     AND A.id <> B.id
     AND A.name < B.name
ORDER BY
  name1,
  name2 ASC;

同一個子查詢使用別名 A 和 B 兩次，組合 reviewers 和 ratings 表並檢索每對評論者-書籍評級的資料。

然後，主外部查詢從子查詢結果中選擇不同的審閱者姓名對。我們在 3 個條件下在子查詢 A 和 B 之間使用 JOIN：

• A.book_id = B.book_id 因此這對評論者對同一本書進行了評分。

• A.id <> B.id 用於過濾掉具有相同 ID 的評論者對，防止自我匹配。

• A.name < B.name< B.name 確保這些對的排序與下面的輸出一致，從而消除重複。這樣，對於給定的一對，只考慮一種名稱組合，例如“Elizabeth Black-Jack Green”，但不考慮“Jack Green-Elizabeth Black”。

這是您將從重構的查詢中獲得的輸出：

+-----------------+-----------------+
|      name1      |      name2      |
+-----------------+-----------------+
| Alice Lewis     | Chris Thomas    |
| Alice Lewis     | Elizabeth Black |
| Alice Lewis     | Jack Green      |
| Alice Lewis     | Joe Martinez    |
| Alice Lewis     | John Smith      |
| Alice Lewis     | Mike White      |
| Chris Thomas    | Elizabeth Black |
| Chris Thomas    | Jack Green      |
| Chris Thomas    | Joe Martinez    |
| Chris Thomas    | John Smith      |
| Chris Thomas    | Mike Anderson   |
| Elizabeth Black | Jack Green      |
| Elizabeth Black | Joe Martinez    |
| Elizabeth Black | John Smith      |
| Elizabeth Black | Mike Anderson   |
| Elizabeth Black | Mike White      |
| Jack Green      | Joe Martinez    |
| Jack Green      | John Smith      |
| Jack Green      | Mike Anderson   |
| Jack Green      | Mike White      |
| Joe Martinez    | John Smith      |
| Joe Martinez    | Mike White      |
| John Smith      | Mike Anderson   |
+-----------------+-----------------+