mysql - 选择产品并加入类别分层

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 次查询相结合的方法。我从这里使用示例

最好的方法是什么？

标签： mysqlsqlmariadb

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