选择产品并加入类别分层

Question

我的数据库中有两个表：

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 RECURSIVE”，但找不到将产品表与1次查询相结合的方法。我使用这里的例子

最好的方法是什么？

Answer 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