재귀적 MySQL

재귀적 MySQL

각 부서의 품목 수량과 함께 카테고리를 반환해야 합니다. 상위 카테고리는 하위 카테고리에서 각 카테고리의 제품 수량을 상속받아야 합니다.

분류

+-------------+----------------------+--------+id | 이름 | parent_id |+-------------+----------------------+--------+1 | 일렉트로닉스 | 특수 |2 | 텔레비전 | 1 |3 | 튜브 | 2 |4 | LCD | 2 |5 | 플라즈마 | 2 |6 | 휴대용 전자 제품 | 1 |7 | MP3 플레이어 | 6 |8 | 플래시 | 7 |9 | CD 플레이어 | 6 |10 | 2WAY 라디오 | 6 |+-------------+----------------------+--------+

제품.

+-------------+----------------------+--------+id | 제품 | 카테고리_id |+-------------+----------------------+--------+1 | TV LCD 32" | 4 |2 | TV LCD 45" | 4 |3 | TV TUBE 29" | 3 |3 | 아이팟 | 7 |+-------------+----------------------+--------+

예상 결과

+-------------+----------------------+------------+id | 이름 | 수준 | 수량 |+-------------+----------------------+------------+1 | 일렉트로닉스 | 1 | 4 |2 | 텔레비전 | 2 | 3 |3 | 튜브 | 3 | 1 |4 | LCD | 3 | 2 |5 | 휴대용 전자 | 2 | 1 |6 | MP3 플레이어 | 3 | 1 |+-------------+----------------------+------------+

중첩을 사용하는 것보다 속도가 훨씬 높기 때문에 재귀적으로 사용해야 합니다.

WITH RECURSIVE category_path (id, name, level, parent_id) AS
(
SELECT id, name, 1 level, parent_id
 FROM categories
  WHERE parent_id IS NULL
 UNION ALL
 SELECT c.id, c.name, level + 1, c.parent_id
  FROM category_path AS cp 
   JOIN categories AS c
    ON cp.id = c.parent_id
)
SELECT * FROM category_path

Time: 0.020s

중첩 사용

SELECT
     parent.id,     
   parent.name,
     parent.parent_id,
   COUNT(departaments.product_id) 
FROM
   categories AS node 
   INNER JOIN
      categories AS parent 
      ON node.lft BETWEEN parent.lft AND parent.rgt 
   INNER JOIN
      departaments 
      ON node.id = departaments.categorie_id 
GROUP BY
   parent.id 
ORDER BY
   node.lft;

Time: 1.510s

먼저 쿼리를 작성하여 범주별 제품 수를 가져옵니다.이는 매우 간단합니다.

with products_per_category as (
  select c.id, count(p.id) as pcount
  from categories c
  left join products p on p.category_id = c.id
  group by c.id
)
  select *
  from products_per_category
  order by id

뒤죽박죽의

그런 다음 재귀 CTE를 작성하여 과도 폐쇄를 생성합니다.

with recursive rcte as (
  select c.id, c.id as ancestor_id
  from categories c
  union all
  select r.id, c.parent_id
  from rcte r
  join categories c on c.id = r.ancestor_id
)
  select *
  from rcte
  order by id, ancestor_id

결과는 다음과 같습니다.

| id  | ancestor_id |
| --- | ----------- |
| 1   | 1           |
| 2   | 1           |
| 2   | 2           |
...
| 9   | 1           |
| 9   | 6           |
| 9   | 9           |
| 10  | 1           |
| 10  | 6           |
| 10  | 10          |

뒤죽박죽의

루트 노드에서 각 범주로 가는 경로를 얻는 것과 같습니다.예를 들어9는 그길입니다.1->6->9

다음 날짜까지 주문하시면ancestor_id먼저, 다음을 얻을 수 있습니다.

| id  | ancestor_id |
| --- | ----------- |
| 1   | 1           |
...
| 10  | 1           |
| 2   | 2           |
| 3   | 2           |
| 4   | 2           |
| 5   | 2           |
| 3   | 3           |
...

뒤죽박죽의

서 그 2 (여기보시바리카고테같이와 2)를 볼 수 .ancestor_id=2 범주가 있습니다.id)2,3,4,5모든 범주에는 하위 범주로 자체가 있습니다.이렇게 하면 다음 단계가 더 간단해집니다.

이제 필요한 것은 두 CTE에 가입하여 제품 수를 합산하는 것입니다.

with recursive products_per_category as (
  select c.id, count(p.id) as pcount
  from categories c
  left join products p on p.category_id = c.id
  group by c.id
), rcte as (
  select c.id, c.id as ancestor_id
  from categories c
  union all
  select r.id, c.parent_id
  from rcte r
  join categories c on c.id = r.ancestor_id
  where c.parent_id is not null
)
  select
    c.id,
    c.name,
    sum(p.pcount) as quantity
  from rcte r
  join categories c on c.id = r.ancestor_id
  left join products_per_category p on p.id = r.id
  group by c.id

결과:

| id  | name                 | quantity |
| --- | -------------------- | -------- |
| 1   | ELECTRONICS          | 4        |
| 2   | TELEVISIONS          | 3        |
| 3   | TUBE                 | 1        |
| 4   | LCD                  | 2        |
| 5   | PLASMA               | 0        |
| 6   | PORTABLE ELECTRONICS | 1        |
| 7   | MP3 PLAYERS          | 1        |
| 8   | FLASH                | 0        |
| 9   | CD PLAYERS           | 0        |
| 10  | 2 WAY RADIOS         | 0        |

뒤죽박죽의

범주를 (예: 빈 범주)quantity = 0), 그런 다음 모든 왼쪽 조인을 안쪽 조인으로 바꿉니다.

갱신하다

▁the를 level SELECTSubquery는 SELECT:할 수 .

(select count(*) from rcte r2 where r2.id = c.id) as level

뒤죽박죽의

언급URL : https://stackoverflow.com/questions/57760697/mysql-with-recursive