mysql - 需要有关 SQL 查询的帮助

Question

这是我的表结构:

id  id_parent   id_origin   level   name1   0   1   1   PHP2   0   2   1   Javascript3   0   3   1   SMARTY4   0   4   1   HTML5   1   1   2   Basic6   1   1   2   Date & Math Function8   2   2   2   DOM9   5   1   3   Introduction10  5   1   3   Session & Cookies12  2   2   2   Introduction13  4   4   2   Basic Structure14  6   1   3   PHP Date Function16  3   3   2   Basic Syntax26  4   4   2   Table

我想要如下格式的结果

Myfinalstr-----------PHPPHP->BasicPHP->Basic->IntroductionPHP->Basic->Session & CookiesPHP->Date & Match FunctionPHP->Date & Match Function->PHP Date FunctionJavascriptJavascript->DOMJavascript->IntroductionSMARTYSMARTY->Basic SyntaxHTMLHTML->Basic StructureHTML->Table

Answer 1

以下不是完整的解决方案，但可以帮助您入门:

示例存储过程调用

mysql> call chapter_hier(1);
+----+----------------------+-----------+----------------------+-------+
| id | category_name        | id_parent | parent_category_name | depth |
+----+----------------------+-----------+----------------------+-------+
|  1 | PHP                  |      NULL | NULL                 |     0 |
|  5 | Basic                |         1 | PHP                  |     1 |
|  6 | Date & Math Function |         1 | PHP                  |     1 |
|  9 | Introduction         |         5 | Basic                |     2 |
| 10 | Session & Cookies    |         5 | Basic                |     2 |
| 14 | PHP Date Function    |         6 | Date & Math Function |     2 |
+----+----------------------+-----------+----------------------+-------+
6 rows in set (0.00 sec)

$result = $conn->query(sprintf("call chapter_hier(%d)", 1));

完整的脚本和测试数据

drop table if exists chapters;
create table chapters
(
id smallint unsigned not null primary key,
name varchar(255) not null,
id_parent smallint unsigned null,
key (id_parent)
)
engine = innodb;

insert into chapters (id, name, id_parent) values
(1,'PHP',null),
(2,'Javascript',null),
(3,'SMARTY',null),
(4,'HTML',null),
(5,'Basic',1),
(6,'Date & Math Function',1),
(8,'DOM',2),
(9,'Introduction',5),
(10,'Session & Cookies',5),
(12,'Introduction',2),
(13,'Basic Structure',4),
(14,'PHP Date Function',6),
(16,'Basic Syntax',3),
(26,'Table',4);


drop procedure if exists chapter_hier;
delimiter #

create procedure chapter_hier
(
in p_id smallint unsigned
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

create temporary table hier(
 id_parent smallint unsigned, 
 id smallint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier select id_parent, id, v_depth from chapters where id = p_id;
create temporary table tmp engine=memory select * from hier;

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

while not v_done do

    if exists( select 1 from chapters c
        inner join tmp on c.id_parent = tmp.id and tmp.depth = v_depth) then

        insert into hier select c.id_parent, c.id, v_depth + 1 from chapters c
            inner join tmp on c.id_parent = tmp.id and tmp.depth = v_depth;

        set v_depth = v_depth + 1;          

        truncate table tmp;
        insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

select 
 c.id,
 c.name as category_name,
 p.id as id_parent,
 p.name as parent_category_name,
 hier.depth
from 
 hier
inner join chapters c on hier.id = c.id
left outer join chapters p on hier.id_parent = p.id
order by
 hier.depth;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

delimiter ;

希望对您有所帮助:)