제품을 선택하고 카테고리를 계층적으로 결합

제품을 선택하고 카테고리를 계층적으로 결합

데이터베이스에 두 개의 테이블이 있습니다.

create table category (id integer, name text, parent_id integer);
create table product (id integer, name text, category integer, description text);

insert into category
        values
        (1, 'Category A', null),
        (2, 'Category B', null),
        (3, 'Category C', null),
        (4, 'Category D', null),
        (5, 'Subcategory Of 1', 1),
        (6, 'Subcategory Of 5', 5),
        (7, 'Subcategory Of 5', 5),
        (8, 'Subcategory of D', 4)
        ;

insert into product
        values
        (1, 'Product One', 5, 'Our first product'),
        (2, 'Product Two', 6, 'Our second product'),
        (3, 'Product Three', 8, 'The even better one');

어떻게 하면 다음과 같이 돌아올 수 있습니까?

product_id | product_name | root_category | category_path               
-----------+--------------+---------------+-----------------------------
         1 | Product One  |             1 | /Category A/Subcategory Of 1
         2 | Product Two  |             1 | /Category A/Subcategory of 5/Subcategory of 6

카테고리 테이블에서 "WITH RECURIC"을 사용하지만 제품 테이블과 1회 쿼리를 결합하는 방법을 찾을 수 없습니다.나는 여기서 예시를 사용합니다.

이것을 하는 가장 좋은 방법은 무엇입니까?

MariaDB 10.2 이상 버전이 있다고 가정하면 여기 있습니다.

with recursive pt (root_id, id, path) as (
  select id, id, concat('/', name) from category where parent_id is null
  union all
  select pt.root_id, c.id, concat(pt.path, '/', c.name) 
    from pt join category c on c.parent_id = pt.id
)
select p.id, p.name, pt.root_id, pt.path
  from pt
  join product p on pt.id = p.category;

결과:

id  name            root_id  path                                                
--  --------------  -------  ---------------------------------------------
1   Product One     1        /Category A/Subcategory Of 1
2   Product Two     1        /Category A/Subcategory Of 1/Subcategory Of 5
3   Product Three   4        /Category D/Subcategory of D

언급URL : https://stackoverflow.com/questions/51539684/select-products-and-join-categories-hierarchical