하나의 SQL 조회에서 모든 상위 행 가져오기

하나의 SQL 조회에서 모든 상위 행 가져오기

범주 목록을 포함하는 간단한 MySQL 테이블이 있으며, 수준은 parent_id에 의해 결정됩니다.

id  name    parent_id
---------------------------
1   Home        0
2   About       1
3   Contact     1
4   Legal       2
5   Privacy     4
6   Products    1
7   Support     1

빵 부스러기 흔적을 만들려고 합니다.그래서 나는 아이의 'id'를 가지고 있고, 나는 모든 이용 가능한 부모를 얻고 싶습니다(0 "Home"에 도달할 때까지 체인을 반복합니다).무제한 깊이로 가는 숫자나 자식 행이 있을 수 있습니다.

현재 저는 부모님별로 SQL콜을 사용하고 있는데요, 엉망진창입니다.SQL에서 하나의 쿼리에 대해 이 모든 것을 수행할 수 있는 방법이 있습니까?

여기서 각색:

SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 5, @l := 0) vars,
        table1 h
    WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

선을@r := 5는 현재 페이지의 페이지 번호입니다.결과는 다음과 같습니다.

1, 'Home'
2, 'About'
4, 'Legal'
5, 'Privacy'

Accepted 답변에는 하위 사용자의 모든 상위 사용자를 재귀적으로 검색할 수 있는 최상의 솔루션이 있습니다.필요에 따라 수정했습니다.

MySQL 5.5, 5.6 및 5.7의 경우

SELECT @r AS user_id, 
   (SELECT @r := parent_id FROM users_table WHERE id = user_id) AS parent_id, 
   @l := @l + 1 AS level 

   FROM (SELECT @r := 9, @l := 0) val, users_table WHERE @r <> 0 

참고 : @r := 9.여기서 9는 하위 사용자의 ID입니다.

여기 fiddle 참조

---

위 쿼리는 MySQL 8에서 더 이상 사용되지 않습니다. 따라서 여기 MySQL 8.0에 대한 쿼리가 있습니다.

with recursive parent_users (id, parent_id, level) AS (
  SELECT id, parent_id, 1 level
  FROM users_table
  WHERE id = 9
  union all
  SELECT t.id, t.parent_id, level + 1
  FROM users_table t INNER JOIN parent_users pu
  ON t.id = pu.parent_id
)
SELECT * FROM parent_users;

참고 : ID = 9.여기서 9는 하위 사용자의 ID입니다.

여기 fiddle 참조

Mark Byers의 멋진 답변!

파티에 좀 늦었을 수도 있지만 id = parent_id일 때(즉, 데이터가 손상된 경우) 무한 루프를 방지하려면 다음과 같이 답변을 확장할 수 있습니다.

    SELECT T2.id, T2.name
    FROM (
        SELECT
            @r AS _id,
            @p := @r AS previous,
            (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
            @l := @l + 1 AS lvl
        FROM
            (SELECT @r := 5, @p := 0, @l := 0) vars,
            table1 h
        WHERE @r <> 0 AND @r <> @p) T1
    JOIN table1 T2
    ON T1._id = T2.id
    ORDER BY T1.lvl DESC

위의 해결책 외에도:

post
-----
id
title
author

author
------
id
parent_id
name


[post]

id  | title | author |  
----------------------
1   | abc   | 3      |


[author]

| id    | parent_id | name  |   
|---------------------------|
| 1     | 0         | u1    |
| 2     | 1         | u2    |
| 3     | 2         | u3    |
| 4     | 0         | u4    |

부모를 포함한 작성자는 게시물에 접근할 수 있습니다.

작성자가 게시물에 접근할 수 있는지 확인하고 싶습니다.

해결책:

저자의 신분증을 주고 그 저자와 저자의 부모를 모두 돌려줍니다.

SELECT T2.id, T2.username 
FROM (
    SELECT @r AS _id, 
        (SELECT @r := parent_id FROM users WHERE id = _id) AS parent_id,
        @l := @l + 1
    FROM
        (SELECT @r := 2, @l := 0) vars, 
        users h     
    WHERE @r <> 0) T1 JOIN users T2 
ON T1._id = T2.id;

@r := 2 => @r 변수에 값을 할당합니다.

smth 가독성을 높이기 위해 이전 답변을 예시로 사용했습니다.

SELECT  @org_id as id,
    (SELECT name FROM test.organizations WHERE id = @org_id) as name,
    (SELECT @org_id := parent_id FROM test.organizations WHERE id = @org_id) AS parent_id
FROM (SELECT @org_id := 4) vars, test.organizations org
WHERE @org_id is not NULL
ORDER BY id;

실행 결과는 다음과 같습니다.

질문의 값을 데이터베이스에 입력해야 직접 확인할 수 있습니다.test,테이블organizations

CREATE TABLE organizations(
id        int(11) NOT NULL AUTO_INCREMENT,
name      varchar(45) DEFAULT NULL,
parent_id int(11)     DEFAULT NULL,
PRIMARY KEY (id));

insert into organizations values(1, "home", null);
insert into organizations values(2, "about", 1);
insert into organizations values(3, "contact", 1);
insert into organizations values(4, "legal", 2);
insert into organizations values(5, "privacy", 4);
insert into organizations values(6, "products", 1);
insert into organizations values(7, "support", 1);

제 생각에는, 하나의 쿼리를 사용하는 것은 쉬운 방법이 없는 것 같습니다.

필요에 맞는 중첩 집합을 확인하는 것이 좋습니다.

ID 대신 슬러그가 있는 경우 하위 쿼리를 실행하여 하위 범주를 찾으십시오.
표 - 카테고리
| id | parentId | slug |
|-------------------------|
| 1 | 0 | u1 |
| 2 | 1 | u2 |
| 3 | 2 | u3 |
| | 4 | 0 | u4 |

SELECT T2.id, T2.slug
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parentId FROM categories WHERE id = _id) AS parentId,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := (SELECT id FROM categories WHERE slug = 'u3'), @l := 0) vars,
        categories h
    WHERE @r <> 0) T1
JOIN categories T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

저는 제 자신의 계층 표에 대한 솔루션을 개발하는 동안 WP 다단계 범주 모델을 살펴보았습니다.여기서 제공된 훌륭한 답변을 바탕으로 워드프레스 데이터베이스의 상위 카테고리를 얻기 위해 이 쿼리를 만들었습니다.저는 이 문제에 대해 전문가는 아니지만 이것은 제 나름대로 효과가 있었고 그러한 답을 찾고 있는 누군가에게 도움이 될 수 있습니다.

  SELECT T2.term_id,T3.name,T3.slug
    FROM (
        SELECT
            @r AS _id,
            @p := @r AS previous,
            (SELECT @r := parent FROM wp_term_taxonomy WHERE term_id = _id AND taxonomy = 'category') AS parent_id,
            @l := @l + 1 AS lvl
        FROM
            (SELECT @r := 8, @p := 0, @l := 0) vars,
            wp_term_taxonomy h
        WHERE @r <> 0 AND @r <> @p) T1
    JOIN wp_term_taxonomy T2 ON T1._id = T2.term_id AND T2.taxonomy = 'category'
    LEFT JOIN wp_terms T3 ON T3.term_id = T2.term_id
    ORDER BY T1.lvl DESC

AFAIK NO.

이 사이트포인트 기사는 도움이 될 것입니다.

하나의 쿼리로 모든 요소를 검색하여 배열에 저장한 다음 여기서 설명하는 것처럼 반복할 수 있습니다.

언급URL : https://stackoverflow.com/questions/2441821/getting-all-parent-rows-in-one-sql-query