分层数据的反向查询

Question

我看过一篇文章http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/，它使用了以下类别表中的数据

  category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |

并返回下面的数据。

  SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
 FROM category AS t1
 LEFT JOIN category AS t2 ON t2.parent = t1.category_id
 LEFT JOIN category AS t3 ON t3.parent = t2.category_id
 LEFT JOIN category AS t4 ON t4.parent = t3.category_id
 WHERE t1.name = 'ELECTRONICS';

 +-------------+----------------------+--------------+-------+
 | lev1        | lev2                 | lev3         | lev4  |
 +-------------+----------------------+--------------+-------+
 | ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
 | ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
 | ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
 | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
 | ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
 | ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
 +-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

现在我的问题是，如果我有一个表或查询，其中包含以下数据

  +-------------+----------------------+--------------+-------+
  | lev1        | lev2                 | lev3         | lev4  |
  +-------------+----------------------+--------------+-------+
  | ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
  | ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
  | ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
  | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
  | ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
  | ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
  +-------------+----------------------+--------------+-------+

如何进行反向查询，返回以下数据：

   category_id | name                 | parent |
   +-------------+----------------------+--------+
 |           1 | ELECTRONICS          |   NULL |
 |           2 | TELEVISIONS          |      1 |
 |           3 | TUBE                 |      2 |
 |           4 | LCD                  |      2 |
 |           5 | PLASMA               |      2 |
 |           6 | PORTABLE ELECTRONICS |      1 |
 |           7 | MP3 PLAYERS          |      6 |
 |           8 | FLASH                |      7 |
 |           9 | CD PLAYERS           |      6 |
 |          10 | 2 WAY RADIOS         |      6 |

如果我还可以有level字段的值(0或1或...)在这个反向查询中做得非常好。

   category_id | name                 | parent |position

   +-------------+----------------------+--------+-------
 |           1 | ELECTRONICS          |   NULL |0
 |           2 | TELEVISIONS          |      1 |0
 |           3 | TUBE                 |      2 |3
 |           4 | LCD                  |      2 |1
 |           5 | PLASMA               |      2 |2

Answer 1

身份证是从哪里来的？假设您有另一个表，每个项目都有ID，那么代码可能如下所示：

select i.ID, l.lev1 as Name, NULL as Parent
from IDTable i 
     join LevelTable l on i.Name = l.lev1
union
select i.ID, l.lev2 as Name, (select j.ID from IDTable j where j.Name = l.lev1)
from IDTable i 
     join LevelTable l on i.Name = l.lev2
union
select i.ID, l.lev3 as Name, (select j.ID from IDTable j where j.Name = l.lev2)
from IDTable i 
     join LevelTable l on i.Name = l.lev3