Блог

Когда я запускаю этот запрос в своей базе данных, я получаю следующее:

 WITH RECURSIVE series AS (
    SELECT CONCAT( a.title) as str, a.prequelID
    FROM ( Prequels NATURAL JOIN Books) AS a
    UNION
    SELECT CONCAT(t.title, ' -> ', str) as str, t.prequelID
FROM (Books NATURAL JOIN Prequels) as t 
INNER JOIN series AS s ON s.prequelID = t.bookID
)
SELECT str as series FROM series
ORDER BY series;
 

Это результат :

Я не хочу никаких дубликатов, только полную строку, которая показывает всю серию. Как мне это сделать?

Обновить:

Я обновил запрос, потому что понял, что мне не хватает первой книги из серии.

 WITH RECURSIVE series AS (
    SELECT CONCAT( a.title) as str, a.prequelID
    FROM ( Prequels NATURAL JOIN Books) AS a
    UNION
    SELECT CONCAT(t.title, ' -> ', str) as str, t.prequelID
FROM (Books NATURAL JOIN Prequels) as t 
INNER JOIN series AS s ON s.prequelID = t.bookID
)
SELECT CONCAT(a.title, ' -> ',a.str) as series
FROM (
SELECT Books.title, series.str
FROM series JOIN Books ON series.prequelID = Books.bookID
WHERE NOT EXISTS(
        SELECT prequelID
        FROM Prequels
        WHERE series.prequelID = Prequels.bookID
) 
) a
ORDER BY series;
 

Результат все еще немного не так, поскольку мне нужна только полная строка серии:

Как мне это исправить?

Таблицы:

 CREATE TABLE Books
(bookID integer PRIMARY KEY,
title varchar(100),
pages integer);


CREATE TABLE Prequels
(bookID INTEGER REFERENCES Books(bookID),
prequelID INTEGER REFERENCES Books(bookID),
PRIMARY KEY (bookID,prequelID));
 

Примеры данных из серии Game of Thornes:

 INSERT INTO BOOKS (bookID,title,pages) VALUES (80429,'A Game of Thrones',292);
INSERT INTO BOOKS (bookID,title,pages) VALUES (41121,'A Clash of Kings',160);
INSERT INTO BOOKS (bookID,title,pages) VALUES (29287,'A Storm of Swords',160);
INSERT INTO BOOKS (bookID,title,pages) VALUES (17696,'A Feast for Crows',292);
INSERT INTO BOOKS (bookID,title,pages) VALUES (3947,'A Dance with Dragons',101);


INSERT INTO Prequels (bookID,prequelID) VALUES (41121,80429);
INSERT INTO Prequels (bookID,prequelID) VALUES (29287,41121);
INSERT INTO Prequels (bookID,prequelID) VALUES (17696,29287);
INSERT INTO Prequels (bookID,prequelID) VALUES (3947,17696);

 

Вы хотели бы начать с корней деревьев.
В приквелидах нет корней.

 WITH RECURSIVE series AS (
    SELECT 
      b.bookId
    , 1 as lvl
    , p.prequelID
    , CONCAT(b.title) as series
    FROM Books AS b
    LEFT JOIN Prequels AS p 
      ON p.bookId = b.bookId
    WHERE NOT EXISTS (
      SELECT 1
      FROM Prequels p2
      WHERE p2.prequelID = b.bookId
    )
    
    UNION ALL
    
    SELECT 
      s.bookId
    , s.lvl 1
    , p.prequelID
    , CONCAT(b.title, ' -> ', s.series)
    FROM series AS s
    JOIN Books AS b 
      ON b.bookId = s.prequelID
    LEFT JOIN Prequels AS p 
      ON p.bookId = s.prequelID
)
SELECT series
FROM series
WHERE prequelId is null
  AND lvl > 1
ORDER BY series;
 

Демонстрация в db<>fiddle здесь

Модифицированная версия LukStorms теперь удалила ответ…

https://dbfiddle.uk/?rdbms=postgres_14amp;fiddle=4e26beed430aefb60f1dc91766db13d3

 WITH RECURSIVE series AS (
   SELECT
      b.bookId,
      1 as lvl,
      p.prequelID,
      CONCAT(b.title)   AS series
    FROM
      books      b
    LEFT JOIN
      prequels   p
        ON p.bookid = b.bookid
    WHERE NOT EXISTS (
      SELECT 1
      FROM Prequels p
      WHERE p.prequelID = b.bookId
    )

    UNION ALL

    SELECT
      b.bookid,
      lvl 1,
      p.prequelID,
      CONCAT(b.title, ' -> ', s.series)
    FROM
      series     s
    INNER JOIN
      books      b
        ON b.bookid = s.prequelid
    LEFT JOIN
      prequels   p
        ON p.bookid = b.bookid
)
SELECT
  s.series
FROM
  series s
WHERE
  s.prequelid IS NULL
ORDER BY
  s.series;
 

Нерекурсивная часть CTE имеет ЛЕВОЕ СОЕДИНЕНИЕ, чтобы включать книги, которые не являются частью серии, просто отдельную книгу. Верните это к ЕСТЕСТВЕННОМУ или ВНУТРЕННЕМУ СОЕДИНЕНИЮ, если вы этого не хотите.

Нерекурсивная часть CTE содержит предложение WHERE, гарантирующее, что она начинается только с последней книги в серии.

Рекурсивная часть CTE имеет ЛЕВОЕ СОЕДИНЕНИЕ в таблице приквелов, так что первая книга в серии не теряется (из-за отсутствия строки приквела), и поэтому записывает приквел этой книги как NULL.

Затем внешний запрос ищет это значение NULL, поэтому выводятся только строки для завершенных рядов.

После рекурсивного запроса вы можете сравнить все строки str вместе с помощью самосоединения и сохранить те s1.str , которые не являются подстрокой any s2.str благодаря WHERE s2.str IS NULL предложению.

 WITH RECURSIVE series AS (
    SELECT CONCAT( a.title) as str, a.prequelID
    FROM ( Prequels NATURAL JOIN Books) AS a
    UNION
    SELECT CONCAT(t.title, ' -> ', str) as str, t.prequelID
FROM (Books NATURAL JOIN Prequels) as t 
INNER JOIN series AS s ON s.prequelID = t.bookID
)
SELECT s1.str as series 
  FROM series AS s1
  LEFT JOIN series AS s2
    ON s2.str ~ s1.str
   AND s2.str <> s1.str
 WHERE s2.str IS NULL
ORDER BY series;
 

результат теста в dbfiddle