Using Recursive Common table expressions to r…
2018-06-17 19:49:07来源：未知 阅读 ()
http://www.postgresonline.com/journal/archives/131-Using-Recursive-Common-table-expressions-to-represent-Tree-structures.html
Tree Problem and was based on PostgreSQL 7.4 technology.
We'll repeat the text here for completeness and demonstrate the PostgreSQL 8.4 that solves this and more efficiently.
Suppose you are tracking supplies and have a field called si_item and another called si_parentid. The parent keeps track of what subclass a supply item belongs to. E.g. you have paper parent that has subclasses such as recycled, non-recycled. When someone takes supplies, you want to return the fully qualified name e.g. Paper->Recycled->20 Lb
Below is what the structure of your table looks like.
si_id int, si_parentid int, si_item. In your table are the following entries
| si_id | si_parentid | si_item |
|---|---|---|
| 1 | Paper | |
| 2 | 1 | Recycled |
| 3 | 2 | 20 lb |
| 4 | 2 | 40 lb |
| 5 | 1 | Non-Recycled |
| 6 | 5 | 20 lb |
| 7 | 5 | 40 lb |
| 8 | 5 | Scraps |
Solution
CREATE TABLE supplyitem(si_id integer PRIMARY KEY, si_parentid integer, si_item varchar(100));
--load up the table (multirow constructor introduced in 8.2)
INSERT INTO supplyitem(si_id,si_parentid, si_item)
VALUES (1, NULL, 'Paper'),
(2,1, 'Recycled'),
(3,2, '20 lb'),
(4,2, '40 lb'),
(5,1, 'Non-Recycled'),
(6,5, '20 lb'),
(7,5, '40 lb'),
(8,5, 'Scraps');
--Recursive query (introduced in 8.4 returns fully qualified name)
WITH RECURSIVE supplytree AS
(SELECT si_id, si_item, si_parentid, CAST(si_item As varchar(1000)) As si_item_fullname
FROM supplyitem
WHERE si_parentid IS NULL
UNION ALL
SELECT si.si_id,si.si_item,
si.si_parentid,
CAST(sp.si_item_fullname || '->' || si.si_item As varchar(1000)) As si_item_fullname
FROM supplyitem As si
INNER JOIN supplytree AS sp
ON (si.si_parentid = sp.si_id)
)
SELECT si_id, si_item_fullname
FROM supplytree
ORDER BY si_item_fullname;
Result looks like
si_id | si_item_fullname ------+----------------------------- 1 | Paper 5 | Paper->Non-Recycled 6 | Paper->Non-Recycled->20 lb 7 | Paper->Non-Recycled->40 lb 8 | Paper->Non-Recycled->Scraps 2 | Paper->Recycled 3 | Paper->Recycled->20 lb 4 | Paper->Recycled->40 lb
A couple of observations:
* Unless the length 1000 has some significance, use TEXT instead of
VARCHAR(1000).
* It might well be both faster and more correct to push items into an array
and use array_to_string() in the outer SELECT, and it won't be subject to
sorting anomalies.
WITH RECURSIVE supplytree AS
(
SELECT
si_id,
si_item,
si_parentid,
ARRAY[si_item] AS si_item_array
FROM supplyitem
WHERE si_parentid IS NULL
UNION ALL
SELECT
si.si_id,si.si_item,
si.si_parentid,
sp.si_item_array || si.si_item As si_item_array
FROM
supplyitem As si
JOIN
supplytree AS sp
ON (si.si_parentid = sp.si_id)
)
SELECT
si_id,
array_to_string(si_item_array, '->') AS si_item_fullname
FROM supplytree
ORDER BY si_item_array;
http://www.postgresql.org/docs/current/static/ltree.html
I'am not saying than WITH RECURSIVE is bad .. just that, there are simpler solution sometimes ;-)
Couple of ways -- you could write a function as we demonstrated in linked article, but that is not as suitable for multiple sets since it would probably do a subquery for each record.
You coulde also take our example and limit with a WHERE clause but that is much slower than it could be.
The other way would be to recurse backward from the child to the parent. So instead of starting at parent nodes -- you start at the child node and keep on unioning until you hit a parent with no parent. Will have to write that up sometime.
--Recursive query (introduced in 8.4 returns fully qualified name)
WITH RECURSIVE supplytree AS
(SELECT si_id, si_item, si_parentid, CAST(si_item As varchar(1000)) As si_item_fullname
FROM supplyitem
WHERE si_item in( '40 lb')
UNION ALL
SELECT si.si_id,si.si_item,
si.si_parentid,
CAST(si.si_item || '->' || sp.si_item_fullname As varchar(1000)) As si_item_fullname
FROM supplyitem As si
INNER JOIN supplytree AS sp
ON (si.si_id = sp.si_parentid)
)
SELECT si_id, si_item_fullname
FROM supplytree where si_parentid is null
ORDER BY si_item_fullname;
table tema
-field tema_id (is the identificator)
-field nombre (is the name)
-field padre_id (is the parent id)
WITH RECURSIVE tema_tree AS (
SELECT tema_id, nombre, padre_id, nombre||'' full_name
FROM tema
WHERE padre_id IS NULL
UNION ALL
SELECT t.tema_id, t.nombre, t.padre_id, tt.full_name||' -> '||t.nombre full_name
FROM tema t
JOIN tema_tree tt ON t.padre_id = tt.tema_id
)
SELECT tema_id, full_name
FROM tema_tree
ORDER BY 2
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
- 单例模式小例子 2018-06-23
- Csharp: read Sybase SQL anywhere5.5 using c# 2018-06-22
- python3连接Mysql时报错1045 using password : NO 2018-06-18
- 事件管理 2018-06-17
- FastReport.Net 2018-06-17
IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash
This post was mentioned on Twitter by roblb: Using Recursive Common table expressions to represent Tree structures: A very long time ago, we wrote .. http://bit.ly/Flne3 #postgres
Tracked: Jan 04, 21:19
Tracked: Aug 20, 00:58