jquery - 多层次Mysql语句

Question

我有一张 table

表Departments

<code>Id                   Name               Parent
1                    Michael Store        0
2                    Nonfood              1
3                    Clothes              2
4                    John Store           0
5                    Shoes                2
6                    Food                 1
7                    Men clothes          3
8                    Shirts               7
9                    Meat                 6
10                   Food                 4</code>



<p>As you see, for each record I have a <em>parent</em> which CAN have it's owen <em>parent</em>. The number of subdivisions is limited to 5. For item 8 (Shirts), you get it's parent (7) which mean that Shirts is part of item with id=7 (Men clothes), part of item with id=3 (Clothes), part of item id=2 (Nonfood), part of id=1 (Michael Store).
I need a Mysql statement with the folowing result:</p>

<p></p>

Id         Name          Sub1          Sub2          Sub3          Sub4          Sub5     
1     Michael Store
2     Michael Store    Nonfood
3     Michael Store    Nonfood       Clothes
4     John Store
5     Michael Store    Nonfood       Shoes
6     Michael Store    Food
7     Michael Store    Nonfood       Clothes      Men clothes 
8     Michael Store    Nonfood       Clothes      Men clothes     Shirts
9     Michael Store    Food          Meat
10    John Store       Food

感谢您的宝贵时间!

Answer 1

非常简单:

SELECT
  d1.id,
  ELT(FIELD(COALESCE(d6.name, d5.name, d4.name, d3.name, d2.name, d1.name), d6.name, d5.name, d4.name, d3.name, d2.name, d1.name), d6.name, d5.name, d4.name, d3.name, d2.name, d1.name) AS Name,
  ELT(FIELD(COALESCE(d6.name, d5.name, d4.name, d3.name, d2.name, d1.name), d6.name, d5.name, d4.name, d3.name, d2.name, d1.name)+1, d6.name, d5.name, d4.name, d3.name, d2.name, d1.name) AS Sub1,
  ELT(FIELD(COALESCE(d6.name, d5.name, d4.name, d3.name, d2.name, d1.name), d6.name, d5.name, d4.name, d3.name, d2.name, d1.name)+2, d6.name, d5.name, d4.name, d3.name, d2.name, d1.name) AS Sub2,
  ELT(FIELD(COALESCE(d6.name, d5.name, d4.name, d3.name, d2.name, d1.name), d6.name, d5.name, d4.name, d3.name, d2.name, d1.name)+3, d6.name, d5.name, d4.name, d3.name, d2.name, d1.name) AS Sub3,
  ELT(FIELD(COALESCE(d6.name, d5.name, d4.name, d3.name, d2.name, d1.name), d6.name, d5.name, d4.name, d3.name, d2.name, d1.name)+4, d6.name, d5.name, d4.name, d3.name, d2.name, d1.name) AS Sub4,
  ELT(FIELD(COALESCE(d6.name, d5.name, d4.name, d3.name, d2.name, d1.name), d6.name, d5.name, d4.name, d3.name, d2.name, d1.name)+5, d6.name, d5.name, d4.name, d3.name, d2.name, d1.name) AS Sub5
FROM department d1
LEFT JOIN department d2 ON d2.id = d1.parent
LEFT JOIN department d3 ON d3.id = d2.parent
LEFT JOIN department d4 ON d4.id = d3.parent
LEFT JOIN department d5 ON d5.id = d4.parent
LEFT JOIN department d6 ON d6.id = d5.parent

查看实际效果: http://sqlfiddle.com/#!2/303a5/20