时间戳

首页 > 解决方案 > 显示基于前传的系列中的所有书籍

sql - 显示基于前传的系列中的所有书籍

问题描述

我有一个包含书籍信息的数据库。我需要创建一个表，其中每一行都是整个系列的列表，看起来像这样

Series |
--------------------------------------------------------------------------------------------------------
The lord of the Rings: The fellowship of the Ring => The Two Towers => The return of the King | 3 | 9250
--------------------------------------------------------------------------------------------------------
Harry Potter: …
--------------------------------------------------------------------------------------------------------

所以整个系列的连接字符串| 系列长度 | 总页数

在我的数据库中，我有 bookid、title、prequeltitle、pages

这是据我所知

With recursive bookseries (title, precededby) as (
Select title, precededby
From books
Where resourceid = 126
Union
Select b.title, b.precededby
From books b
Inner join bookseries bs on bs.precededby = b.title 
)
Select * from bookseries;

 title                             |                          precededby
--------------------------------------------------------------+--------------------------------------------------------------
 Harry Potter and the Deathly Hallows                         | Harry potter and the Half-Blood Prince
 Harry potter and the Half-Blood Prince                       | Harry Potter and the Order of the Pheonix
 Harry Potter and the Order of the Pheonix                    | Harry Potter and the Goblet of Fire
 Harry Potter and the Goblet of Fire                          | Harry Potter and the Prisoner of Azkaban
 Harry Potter and the Prisoner of Azkaban                     | Harry Potter and the Chamber of Secrets
 Harry Potter and the Chamber of Secrets                      | Harry Potter and the Philosphers Stone
 Harry Potter and the Philosphers Stone                       |
(7 rows)

resourceid=126 只是为了从我的数据库中获取最后一本书

CREATE TABLE Books ( 
ResourceID int PRIMARY KEY, 
Title char(60), 
pages int,
Isbn char (30), 
AuthorID int REFERENCES Authors(AuthorID), 
Edition int, 
Language char (30),
Publisher char (60), 
DateOfPublication date, 
PrecededBy char (60)
);

标签： sqlpostgresql

解决方案

你离需要的东西不远。您需要STRING_AGG函数而不是 CONCAT，并对递归 CTE 稍作改动。我将在下面显示结果，但首先在桌面上提出一些建议。<br/>

  1. 不要使用 CHAR 类型，而是使用 VARCHAR 或 TEXT。请参阅文档。这实际上适用于数据库中的所有表。
  2. 不要使用 PrecededBy 的书名。而是将其设为一个整数，其中包含前任的 ResourceID 和它的 FK。这确保了前身确实存在。

现在查询：

with recursive bookseries (title, pages, seq) as 
   (select title,pages, 1
      from books
     where title = 'Harry Potter and the Philosphers Stone'
    union all
    select b.title, b.pages, seq+1
      from books b
      join bookseries bs on b.precededby = bs.title 
   ) 
select string_agg( title, ' -> '  order by seq) as book_series
     , max(seq) number_of_books
     , sum(pages) total_pages 
  from bookseries;

推荐阅读