Comment supprimer les paires sélectionnées dans votre propre table

Question

J'ai moi-même rejoint une table et j'ai les paires en double mises en évidence dans l'image ci-dessous, comment puis-je les supprimer ?

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

Nom 1	Nom 2
Alice Lewis	Elizabeth Blake
Chris Thomas	John Smith
Chris Thomas	Mike Blanc
Elizabeth Blake	Alice Lewis
Elizabeth Blake	Jack Vert
Jack Vert	Elizabeth Blake
Joe Martinez	Mike Anderson
John Smith	Chris Thomas
Mike Anderson	Joe Martinez
Mike Blanc	Chris Thomas

Le tableau ci-dessus était autrefois une image

Answer 1

Vous pouvez le faire

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

Voir cet exemple

Answer 2

J'ai créé des instructions DDL et DML pour reproduire la base de données et des requêtes écrites pour récupérer des paires uniques. Voici le code "build" qui pourrait aider les autres :

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);

Voici la requête refactorisée :

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;

La même sous-requête utilise les alias A et B deux fois, combinant les tables reviewers 和 ratings et récupérant les données pour chaque paire de notes critique-livre.

La requête externe principale sélectionne ensuite des paires de noms de réviseurs distinctes à partir des résultats de la sous-requête. On utilise JOIN entre les sous-requêtes A et B sous 3 conditions :

• A.book_id = B.book_id Cette paire de critiques a donc noté le même livre.

• A.id <> B.id Utilisé pour filtrer les paires de réviseurs avec le même identifiant afin d'éviter l'auto-appariement.

• A.name < B.name Assurez-vous que les paires sont ordonnées conformément au résultat ci-dessous, éliminant ainsi les doublons. De cette façon, pour une paire donnée, une seule combinaison de noms est prise en compte, par exemple « Elizabeth Black-Jack Green », mais pas « Jack Green-Elizabeth Black ».

Voici le résultat que vous obtiendrez de la requête restructurée :

+-----------------+-----------------+
|      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   |
+-----------------+-----------------+