MySQL 테이블은 다음과 같습니다.
id | name | parent_id
19 | category1 | 0
20 | category2 | 19
21 | category3 | 20
22 | category4 | 21
......
이제 ID를 제공하는 MySQL 쿼리 하나만 있으면 됩니다(예: 'id = 19'). 그러면 모든 하위 ID를 얻어야 합니다(예: result에는 ID '20,21,22'). 또한, 아이들의 계급은 다양할 수 있다는 것을 알 수 없다.
그리고, 나는 이미 for 루프를 사용하는 해결책을 가지고 있다. 가능하다면 하나의 MySQL 쿼리를 사용하여 동일한 결과를 얻을 수 있는 방법을 알려주세요.
그런 다음, MySQL 을 사용하는 경우 8 반복 ['을 (를)'] (https://dev.mysql.com/doc/refman/8.0/en/with.html) 조항.
with recursive cte (id, name, parent_id) as (
select id,
name,
parent_id
from products
where parent_id = 19
union all
select p.id,
p.name,
p.parent_id
from products p
inner join cte
on p.parent_id = cte.id
)
select * from cte;
'가치' 에 지정된 부모 'id' = 19 parent_id 설정되어야 합니다 모든 후손 선택할 수 있다.
Mysql 용 버전 (버전 5.7 최대) 를 지원하지 않는 일반 테이블 표현식에서는 쿼리하지 다음 이를 수행할 수 있습니다.
select id,
name,
parent_id
from (select * from products
order by parent_id, id) products_sorted,
(select @pv := '19') initialisation
where find_in_set(parent_id, @pv)
and length(@pv := concat(@pv, ',', id))
이것은 [바이올린] (http://sqlfiddle.com/ #! 9/d74210/1). 여기서 & # 39 에서 지정한 값을 @pv: = ',', 'id' 부모 19& # 39 설정되어야 합니다 모든 후손 선택할 수 있다. 이 경우에도 사용할 수 있습니다 모 여러 명의 자녀를 두고 있다. 그러나 각 레코드가 조건 'parent_id < 충족합니다 있어야 합니다. id ', 그 결과 완료되지 않습니다.
Mysql 이 질의에서는 /dev/raw/raw1 특정 문법: 그 동안 수정되었음 com/go/4e6b330a_kr 할당되었는지 및 실행. 실행 순서를 대한 몇 가지 가정을 하고 있다.
이 솔루션은 느린 대용량 데이터 집합을 얻기 위해 수 있으므로, ['find_in_set'] (https://dev.mysql.com/doc/refman/8.0/en/string-functions.html # function_find-in-set) 작업은 여러 목록에 포함되지 않은 것은 분명 찾을 수 있는 방법이 가장 이상적인 크기 같은 순서로 도달하는 바뀌엇어요 큰 수로 레코드는유지합니다 반환되었습니다.
점점 더 많은 데이터베이스 구축 [sql 1999년 iso 표준 '을 (를) [재귀]' 구문을] (https://en.wikipedia.org/wiki/SQL): 1999년 # common_table_expressions_and_recursive_queries) 재귀적 쿼리합니다 (예: [postgres 8.4+] (http://www.postgresql.org/docs/8.4/static/queries-with.html), [sql server 2005년 +] (https://technet.microsoft.com/en-us/library/ms186243%28v = sql.105%29.aspx), [db2] (https://www.ibm.com/developerworks/ibmi/library/i-db2connectby/), [oralce 11gr2+] (https://docs.oracle.com/cloud/latest/db112/SQLRF/statements_10002.htm # sthref6733), [sqlite 3.8.4+] (https://www.sqlite.org/lang_with.html), [파이어버드 2.1+] (https://firebirdsql.org/refdocs/langrefupd21-select.html # langrefupd21 스테 선택합니다.), [h2] (http://www.h2database.com/html/advanced.html # recursive_queries), [히페르스크라 2.1.0+] (http://www.hsqldb.org/doc/guide/dataaccess-chapt.html # dac_with_clause), [teradata] (http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html # page/sql_reference/b035_1146_111a/ch01.032.066.html), [mariadb 10.2.2+] (https://mariadb.com/kb/en/mariadb/with/)). [MySQL 은 버전 8.0) 도 it] 및 vmware. (https://dev.mysql.com/doc/refman/8.0/en/with.html). 이 문제에 대한 상단형 참조 구문 사용할 수 있습니다. 대체, 비표준 구문에서 등 일부 데이터베이스에는 계층적이지 업 봐 ',' 조항을 통해 접속하십시오 support. dell. [Oracle] (https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm), [DB2] (https://www.ibm.com/developerworks/ibmi/library/i-db2connectby/), [nformix] (https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_2035.htm), [CUBRID] (https://www.cubrid.org/manual/en/10.0/sql/query/hq.html) 및 기타) 이다. 버전 5.7 mysql 같은 기능을 제공하지 않는다. 이 경우 데이터베이스 엔진 는 구문 또는 방정식입니다 크기:, 그 것은 마이그레이션해야 수 있습니다) 가 가장 좋은 옵션입니다. 그렇지 않으면 또 대안을 고려할 사항은 다음과 같습니다.
Id '이 포함된' 하면 될 것을 훨씬 쉽게 할당할지 값뿐 계층적이지 정보: 패스. 예를 들어, 이렇게 네 경우엔 이 보일 수 있다.
ID | NAME
19 | category1
19/1 | category2
19/1/1 | category3
19/1/1/1 | category4
그런후에도 너회의 '선택' 다시그것들을 다음과 같습니다.
select id,
name
from products
where id like '19/%'
해당 계층에 대한 상한을 알고 있다면 얼마나 깊은 사용할 수 있습니다 '표준' sql 쿼리 다음과 같은 트리별 될 수 있습니다.
select p6.parent_id as parent6_id,
p5.parent_id as parent5_id,
p4.parent_id as parent4_id,
p3.parent_id as parent3_id,
p2.parent_id as parent2_id,
p1.parent_id as parent_id,
p1.id as product_id,
p1.name
from products p1
left join products p2 on p2.id = p1.parent_id
left join products p3 on p3.id = p2.parent_id
left join products p4 on p4.id = p3.parent_id
left join products p5 on p5.id = p4.parent_id
left join products p6 on p6.id = p5.parent_id
where 19 in (p1.parent_id,
p2.parent_id,
p3.parent_id,
p4.parent_id,
p5.parent_id,
p6.parent_id)
order by 1, 2, 3, 4, 5, 6, 7;
이 슬라이드에서는 [바이올린] (http://sqlfiddle.com/ #! 9/5de2a/46) 여기서 '조건' 이 하위 항목이 읽어들이려면 운영까지도 모피쳐 지정합니다. 이 질의에서는 레벨뿐 필요에 따라 더욱 확장할 수 있습니다.
블로그 MySQL에서 계층 데이터 관리에서
테이블 구조
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
쿼리:
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
출력
+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+
대부분의 사용자는 SQL 데이터베이스의 계층 데이터를 한 번 이상 다뤄본 적이 있으며 계층 데이터의 관리가 관계형 데이터베이스가 의도하는 바가 아니라는 것을 알게 되었다. 관계형 데이터베이스의 테이블은 (XML과 같은) 계층적이지 않고 단지 플랫 리스트일 뿐이다. 계층적 데이터는 관계형 데이터베이스 테이블에 자연스럽게 표현되지 않는 상위-하위 관계를 가집니다. 자세히 읽어보기
자세한 내용은 블로그를 참조하십시오.
편집:
select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv
출력:
category_id name parent
19 category1 0
20 category2 19
21 category3 20
22 category4 21
참조: [Mysql에서 재귀 SELECT 쿼리를 수행하는 방법]]3
이러한 시도하시겠습니까.
테이블 정의:
DROP TABLE IF EXISTS category;
CREATE TABLE category (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
parent_id INT,
CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
REFERENCES category (id)
) engine=innodb;
실험 행:
INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);
재귀 저장 프로시저:
DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
DECLARE catname VARCHAR(20);
DECLARE temppath TEXT;
DECLARE tempparent INT;
SET max_sp_recursion_depth = 255;
SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
IF tempparent IS NULL
THEN
SET path = catname;
ELSE
CALL getpath(tempparent, temppath);
SET path = CONCAT(temppath, '/', catname);
END IF;
END$$
DELIMITER ;
래퍼 대한 함수를 저장 프로시저:
DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE res TEXT;
CALL getpath(cat_id, res);
RETURN res;
END$$
DELIMITER ;
일부 예:
SELECT id, name, getpath(id) AS path FROM category;
출력:
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1 |
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA |
| 24 | categoryB | category1/categoryA/categoryB |
| 25 | categoryC | category1/categoryA/categoryC |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+
필터링부터 행뿐만 일부 경로:
SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';
출력:
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+
내가 생각해낸 최고의 접근법은
계보 접근법 설명자는 예를 들어 어디에서나 찾을 수 있다. 여기 또는 여기. 기능에 관해서는 -그것이 나를 흥분시켰다.
결과적으로 다소 단순하고 비교적 빠르고 간단한 솔루션을 얻을 수 있습니다.
함수'의 본문
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
READS SQL DATA
BEGIN
DECLARE v_rec INT DEFAULT 0;
DECLARE done INT DEFAULT FALSE;
DECLARE v_res text DEFAULT '';
DECLARE v_papa int;
DECLARE v_papa_papa int DEFAULT -1;
DECLARE csr CURSOR FOR
select _id,parent_id -- @n:=@n+1 as rownum,T1.*
from
(SELECT @r AS _id,
(SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := the_id, @l := 0,@n:=0) vars,
table m
WHERE @r <> 0
) T1
where T1.parent_id is not null
ORDER BY T1.lvl DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open csr;
read_loop: LOOP
fetch csr into v_papa,v_papa_papa;
SET v_rec = v_rec+1;
IF done THEN
LEAVE read_loop;
END IF;
-- add first
IF v_rec = 1 THEN
SET v_res = v_papa_papa;
END IF;
SET v_res = CONCAT(v_res,'-',v_papa);
END LOOP;
close csr;
return v_res;
END
그리고 넌 그냥
select get_lineage(the_id)
누군가에게 도움이 되길 바래 :)
여기서 또 한 케이론 같은 것입니다.
쿼리는 다음과 같이 쓸 수 있다.
SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(
SELECT GROUP_CONCAT(id SEPARATOR ',')
FROM table WHERE parent_id IN (@pv)
) AS lv FROM table
JOIN
(SELECT @pv:=1)tmp
WHERE parent_id IN (@pv)
) a;
읽기 속도를 빠르게 하는 경우 가장 좋은 방법은 폐쇄 테이블을 사용합니다. 상위 / 하위 페어당 행을 각 테이블에 대한 폐쇄. 그래서 같은 것이어서 테이블 엿볼 수 있는 (예:
ancestor | descendant | depth
0 | 0 | 0
0 | 19 | 1
0 | 20 | 2
0 | 21 | 3
0 | 22 | 4
19 | 19 | 0
19 | 20 | 1
19 | 21 | 3
19 | 22 | 4
20 | 20 | 0
20 | 21 | 1
20 | 22 | 2
21 | 21 | 0
21 | 22 | 1
22 | 22 | 0
이 테이블을 후에는 계층적이지 쿼리합니다 매우 빠르고 쉽게 된다. 하위 항목이 모두 실현할 수 범주입니다 20:
SELECT cat.* FROM categories_closure AS cl
INNER JOIN categories AS cat ON cat.id = cl.descendant
WHERE cl.ancestor = 20 AND cl.depth > 0
물론 이 같은 데이터 비정규 사용할 때마다 큰 문제 없다. 폐쇄 테이블 표에 너회의 범주입니다 함께 관리해야 합니다. 아마도 가장 좋은 방법은 사용할 수 있지만, 다소 복잡한 트리거합니다 제대로 추적되도록 삽입물의 / 업데이트 / deletes 폐쇄를 위한 표. 아무 것도 마찬가지로 요구 사항을 결정하는 방식이 가장 적합한 살펴 보아야 합니다.
select @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where parent_id=@pv
결과:.
id name parent_id
20 category2 19
21 category3 20
22 category4 21
26 category24 22
. 를 왼쪽 bigadmin:
select
@pv:=p1.id as id
, p2.name as parent_name
, p1.name name
, p1.parent_id
from products p1
join (select @pv:=19)tmp
left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
where p1.parent_id=@pv
select id,
name,
parent_id
from (select * from products
order by parent_id, id) products_sorted,
(select @pv := '19') initialisation
where find_in_set(parent_id, @pv) > 0
and @pv := concat(@pv, ',', id)
[1] 와 [Sql 바이올린] 온라인으로 테스트 결과 모두 볼 수 있습니다.
9/a318e3/4/0 http://sqlfiddle.com/ #!
[1]: 9/a318e3/4/0 http://sqlfiddle.com/ #!
이와 같은 매우 쉽게 할 수 있는 다른 데이터베이스뿐 반복 질의 (이메프 성능에 대한).
두 개의 추가 비트의 데이터를 다른 길일 저장입니다, 왼쪽 및 오른쪽 값입니다. 왼쪽 및 오른쪽 값은 트리 구조를 가지고 있었기 you& # 39 에서 파생됨 횡단, 사이트용.
이것은 간단한 쿼리를 실행할 수 있고 수정된 것으로 알고 선주문 트리 순회 및 모피쳐 값을 한 번에 모두 수행할 수 있습니다. 또한 댁이라면 이름으로 네스트된 set" ";).
조금 까다롭네요, 잘 되는지 확인해 보세요.
select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='') as c having list like '%19,%';
SQL fidl 링크 http://www.sqlfiddle.com/#2/e3cdf/2
필드 및 테이블 이름으로 적절하게 대체합니다.
그냥 브루제 / 진단트리는 클래스를 사용할 수 있도록 한 테이블에 자체 관계 진단트리는 php, mysql.
>. # # # 160; Tree\Node& and& tree& 160, PHP, 상위 id 를 사용하여 데이터 처리를 위한 구조화된 계층적으로 클래스뿐만 160 있다 즉 참조입니다. 그 대표적인 예가 테이블 관계형 데이터베이스에 레코드 "모" 여기서 각 필드는 기본 키 참조입니다 다른 기록. 물론 데이터베이스에서 데이터를 사용할 수 없는 것도 있지만 진단트리는 디렉토리에만 된다. 제공한 데이터 및 트리별 /dev/raw/raw1 위치에 상관없이 데이터 처리 및 it came from "어떻게. 자세한 정보
/ 진단트리는 브루제 사용하는 예를 들어보겠습니다.
<?php
require '/path/to/vendor/autoload.php'; $db = new PDO(...); // Set up your database connection
$stm = $db->query('SELECT id, parent, title FROM tablename ORDER BY title');
$records = $stm->fetchAll(PDO::FETCH_ASSOC);
$tree = new BlueM\Tree($records);
...
, 범주입니다 it& s # 39 표.
SELECT id,
NAME,
parent_category
FROM (SELECT * FROM category
ORDER BY parent_category, id) products_sorted,
(SELECT @pv := '2') initialisation
WHERE FIND_IN_SET(parent_category, @pv) > 0
AND @pv := CONCAT(@pv, ',', id)
여기서 말하는 것은 뭔가 조금 있지만, 이 두 번째 방법도 달라야 오토메이티드 저렴한 비용의 유사한 수락됨 넓은 계층 (삽입하십시오 삭제하시겠습니까 업데이트하십시오) 항목을 쿼리하고 쉽냐구요 약간만이라도 추가에는 영구 경로 열 각 항목에 대해.
일부 다음과 같습니다.
id | name | path
19 | category1 | /19
20 | category2 | /19/20
21 | category3 | /19/20/21
22 | category4 | /19/20/21/22
예:
-- get children of category3:
SELECT * FROM my_table WHERE path LIKE '/19/20/21%'
-- Reparent an item:
UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'
// base10 => base36
'1' => '1',
'10' => 'A',
'100' => '2S',
'1000' => 'RS',
'10000' => '7PS',
'100000' => '255S',
'1000000' => 'LFLS',
'1000000000' => 'GJDGXS',
'1000000000000' => 'CRE66I9S'
https://en.wikipedia.org/wiki/Base36
/ # 39, & # 39 억제하는 효과가 있다. 고정 길이 및 패딩 인코딩되지 id 를 사용하여 분리자의
자세한 설명 최적화이든 있습니다. https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/
라고 한 항목씩 검색 경로를 구축하는 함수 또는 프로시저에서는 보장-분리
이 날, 이 방법은 너무 당신꺼에요 사용할 수 있다. 이 아이를 Root 로 최고기록을 너희에의 부여하느뇨 특정 메뉴. 필드 이름 변경 요구 사항에 따라.
SET @id:= '22';
SELECT Menu_Name, (@id:=Sub_Menu_ID ) as Sub_Menu_ID, Menu_ID
FROM
( SELECT Menu_ID, Menu_Name, Sub_Menu_ID
FROM menu
ORDER BY Sub_Menu_ID DESC
) AS aux_table
WHERE Menu_ID = @id
ORDER BY Sub_Menu_ID;
더 쉽게 찾았어.
is_related(id, parent_id);
in your 예
is_related(21, 19) == 1;
is_related(20, 19) == 1;
is_related(21, 18) == 0;
select ...
from table t
join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id));
내가 한 예증의표적으로 쿼리하지 있습니다. 이 한 번의 쿼리를 반복 범주입니다 부여하느뇨 있습니다.
SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 WHERE prent is NULL
UNION
SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL
UNION
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL
UNION
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL
ORDER BY NAME,subName,subsubName,subsubsubName
이것은 [바이올린] [1].
[1]: 9/b4f997/3 http://sqlfiddle.com/ #!