Heim  >  Fragen und Antworten  >  Hauptteil

So löschen Sie ausgewählte Paare in einer eigenen Tabelle

Ich habe mich selbst einer Tabelle angeschlossen und die doppelten Paare sind im Bild unten hervorgehoben. Wie kann ich sie löschen?

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
Name 1 Name 2
Alice Lewis Elizabeth Blake
Chris Thomas John Smith
Chris Thomas Mike White
Elizabeth Blake Alice Lewis
Elizabeth Blake Jack Green
Jack Green Elizabeth Blake
Joe Martinez Mike Anderson
John Smith Chris Thomas
Mike Anderson Joe Martinez
Mike White Chris Thomas

Die obige Tabelle war einmal ein Bild

P粉432930081P粉432930081220 Tage vor431

Antworte allen(2)Ich werde antworten

  • P粉481366803

    P粉4813668032024-04-02 15:27:14

    你可以这样做

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

    请参阅此示例

    Antwort
    0
  • P粉141035089

    P粉1410350892024-04-02 14:25:20

    我已经创建了 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 确保这些对的排序与下面的输出一致,从而消除重复。这样,对于给定的一对,仅考虑一种名称组合,例如“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   |
    +-----------------+-----------------+
    

    Antwort
    0
  • StornierenAntwort