在SQL中处理树结构 [英] Handle tree structure in SQL

问题描述

我必须处理一个表，它是为了适应产品的树形结构而创建的。这样做是为了处理一个产品可以包含多个其他产品的情况（例如，一个产品包含多个其他位置）。因此，我正在制作一个接受OrderDetails的功能，它必须遍历所有产品并列出所列产品的子产品。我正面临一个问题，我必须遍历未知深度的树。请告诉我该怎么做。



UPD。试图在PostgreSQL中实现这样的结构，这里是代码：

I have to handle a table PRODUCTS which is created to accommodate tree structure of products. It is done to handle situations when one product can contain several others (e.g. one package product holds several other positions). So, I'm making a function that takes OrderDetails, and it must iterate through all PRODUCTS and list out the child products for each product listed. I am facing an issue that I have to iterate through tree of unknown depth. Please, give me an idea how to do it.

UPD. Tried to implement such structure in PostgreSQL, here is the code:

CREATE TABLE public.tree_products (
  product_id INTEGER DEFAULT nextval('ree_products_product_id_seq'::regclass) NOT NULL,
  name VARCHAR,
  parent_id INTEGER,
  CONSTRAINT ree_products_pkey PRIMARY KEY(product_id)
) 

CREATE OR REPLACE FUNCTION foo()RETURNS text AS
$body$
DECLARE _row RECORD;
		_result text := '';
        _child_row RECORD;
        _count integer := -1;
        _marker integer := 1;
BEGIN
	FOR _row IN SELECT * FROM tree_products
    LOOP
    	_result := _result || _marker || ' ' || _row.name;
    	_count := (SELECT count(product_id) FROM tree_products WHERE parent_id = _row.product_id);
        IF _count > 0 THEN
        	FOR _child_row IN SELECT * FROM tree_products WHERE parent_id = _row.product_id
            LOOP
            	_result := _result || ' ' || _child_row.name;
            END LOOP;
        END IF;
        _marker := _marker =1;   	
   	END LOOP;
END;
$body$
LANGUAGE plpgsql

该功能通过产品表进行，但是孩子搜索的深度限制为1.我想拥有无限的列表深度。

That function goes throuh products table, but the depth of childs search is limited to 1. I want to have unlimited depth of listing.

推荐答案

body

DECLARE _row RECORD;
_result text ：= ' ';
_child_row RECORD;
_count integer：= -1;
_marker integer：= 1 ;
BEGIN
FOR _row IN SELECT * FROM tree_products
LOOP
_result：= _result | | _marker || ' ' || _row.name;
_count：=（ SELECT count（product_id） FROM tree_products WHERE parent_id = _row.product_id）;
IF _count> 0 那么
FOR _child_row IN SELECT * FROM tree_products WHERE parent_id = _row.product_id
LOOP
_result：= _result || ' ' || _child_row.name;
END LOOP;
END IF ;
_marker：= _marker = 1;
END LOOP;
END ;

DECLARE _row RECORD; _result text := ''; _child_row RECORD; _count integer := -1; _marker integer := 1; BEGIN FOR _row IN SELECT * FROM tree_products LOOP _result := _result || _marker || ' ' || _row.name; _count := (SELECT count(product_id) FROM tree_products WHERE parent_id = _row.product_id); IF _count > 0 THEN FOR _child_row IN SELECT * FROM tree_products WHERE parent_id = _row.product_id LOOP _result := _result || ' ' || _child_row.name; END LOOP; END IF; _marker := _marker =1; END LOOP; END;

body

这篇关于在SQL中处理树结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！