Mysql 多重连接来描绘我的类别的表布局

Question

我试图描绘一个类别列表，子目录和子目录等等。

Root             Sub Level 1
Personal income  Adelaide IT Solutions (AITS)
Personal income  DJ
Personal income  Ebay Sales
Personal income  IAG
Rental income    Cash Grove Pasadena
Rental income    Winston Ave Cumberland Park
Personal income  Misc Income

我编写了以下返回结果的 SQL 语句，但仅当它与联接匹配时才有效，例如，如果我有一个深度为 3 层的联接，则它只会列出具有该深度类别的类别。

我假设我必须使用嵌套选择或类似的东西，但不知道如何去做。

SELECT
    c.name as 'Root',
    s.name as 'Sub Level 1',
    s2.name as 'Sub Level 2',
    s3.name as 'Sub Level 3'
FROM catergories c
    INNER JOIN catergories s ON c.id = s.parent
    INNER JOIN catergories s2 ON s.id = s2.parent
    INNER JOIN catergories s3 ON s2.id = s3.parent

任何帮助将不胜感激。

Answer 1

相当简单的非递归邻接列表存储过程实现，允许您从应用程序层对数据库进行一次调用并检索所需的层次结构。

mysql> call employees_hier(1);
+--------+-----------------+-------------+-----------------+-------+
| emp_id | emp_name        | boss_emp_id | boss_name       | depth |
+--------+-----------------+-------------+-----------------+-------+
|      1 | f00             |        NULL | NULL            |     0 |
|      2 | ali later       |           1 | f00             |     1 |
|      3 | megan fox       |           1 | f00             |     1 |
|      4 | jessica alba    |           3 | megan fox       |     2 |
|      5 | eva longoria    |           3 | megan fox       |     2 |
|      6 | keira knightley |           5 | eva longoria    |     3 |
|      7 | liv tyler       |           6 | keira knightley |     4 |
|      8 | sophie marceau  |           6 | keira knightley |     4 |
+--------+-----------------+-------------+-----------------+-------+
8 rows in set (0.00 sec)

显然用你的类别替换员工......

drop table if exists employees;
create table employees
(
emp_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
boss_id smallint unsigned null,
key (boss_id)
)
engine = innodb;

insert into employees (name, boss_id) values
('f00',null), 
  ('ali later',1), 
  ('megan fox',1), 
      ('jessica alba',3), 
      ('eva longoria',3), 
         ('keira knightley',5), 
            ('liv tyler',6), 
            ('sophie marceau',6);


drop procedure if exists employees_hier;

delimiter #

create procedure employees_hier
(
in p_emp_id smallint unsigned
)
begin

declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);

create temporary table hier(
 boss_id smallint unsigned, 
 emp_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select boss_id, emp_id, v_dpth from employees where emp_id = p_emp_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table emps engine=memory select * from hier;

while not v_done do

    if exists( select 1 from employees e inner join hier on e.boss_id = hier.emp_id and hier.depth = v_dpth) then

        insert into hier select e.boss_id, e.emp_id, v_dpth + 1 
            from employees e inner join emps on e.boss_id = emps.emp_id and emps.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table emps;
        insert into emps select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;

select 
 e.emp_id,
 e.name as emp_name,
 p.emp_id as boss_emp_id,
 p.name as boss_name,
 hier.depth
from 
 hier
inner join employees e on hier.emp_id = e.emp_id
left outer join employees p on hier.boss_id = p.emp_id;

drop temporary table if exists hier;
drop temporary table if exists emps;

end #

delimiter ;

-- call this sproc from your php

call employees_hier(1);

Answer 2

假设最大深度为 3 层，最简单的实现是使用 LEFT JOIN 来显示 Root 列中的所有类别及其子类别（如果存在）：

SELECT
 c.name as 'Root',
 s.name as 'Sub Level 1',
 s2.name as 'Sub Level 2',
 s3.name as 'Sub Level 3'
FROM catergories c
 LEFT JOIN catergories s ON c.id = s.parent
 LEFT JOIN catergories s2 ON s.id = s2.parent
 LEFT JOIN catergories s3 ON s2.id = s3.parent

通过上面的查询，您将有许多重复项 -所有类别将显示为根，其中一些类别将再次显示为各个级别的子类别。要只有父母作为 root，我想你可以添加规则：

SELECT
 c.name as 'Root',
 s.name as 'Sub Level 1',
 s2.name as 'Sub Level 2',
 s3.name as 'Sub Level 3'
FROM catergories c
 LEFT JOIN catergories s ON c.id = s.parent
 LEFT JOIN catergories s2 ON s.id = s2.parent
 LEFT JOIN catergories s3 ON s2.id = s3.parent
WHERE c.parent IS NULL  
-- or parent=-1, or whatever a category with no parent has for in the parent column