请教一个三级父子关联查询的SQL语句？

[代码誊写工]

请教各位一个查询。
有三个表，分别是device，device_group，device_interface，它们的内容和结构如下：

DB> SELECT * FROM `device`;
+----+-----------------+-------------+-----------------+--------+------+-----------+-----------------+
| id | name            | description | address         | status | flag | order_seq | data_server     |
+----+-----------------+-------------+-----------------+--------+------+-----------+-----------------+
|  1 | 192_168_200_90  | vmprobe     | 192.168.200.90  |      1 |    1 |         0 | 192.168.200.90  |
|  2 | 192_168_200_100 | vmprobe2    | 192.168.200.100 |      1 |    1 |         0 | 192.168.200.100 |
+----+-----------------+-------------+-----------------+--------+------+-----------+-----------------+
2 rows in set (0.00 sec)

DB> SELECT * FROM `device_group`;
+----+-----------+------+-------------+----------------+--------+-----------+----------------+-----------------+-------------+
| id | device_id | name | description | interface_type | status | order_seq | num_mm_session | num_pdp_session | num_session |
+----+-----------+------+-------------+----------------+--------+-----------+----------------+-----------------+-------------+
|  1 |         1 | if32 |             |              0 |      0 |         0 |              0 |               0 |           0 |
|  2 |         1 | if33 |             |              0 |      0 |         0 |              0 |               0 |           0 |
|  3 |         2 | if32 |             |              0 |      0 |         0 |              0 |               0 |           0 |
|  4 |         2 | if33 |             |              0 |      0 |         0 |              0 |               0 |           0 |
+----+-----------+------+-------------+----------------+--------+-----------+----------------+-----------------+-------------+
4 rows in set (0.00 sec)

DB> SELECT * FROM `device_interface`;
+----+-----------+----------+-----------+------+--------+-----------+
| id | device_id | group_id | link_type | name | status | order_seq |
+----+-----------+----------+-----------+------+--------+-----------+
|  1 |         1 |        1 |         2 | if0  |      0 |         0 |
|  2 |         1 |        1 |         2 | if1  |      0 |         0 |
|  3 |         1 |        2 |         2 | if2  |      0 |         0 |
|  4 |         1 |        2 |         2 | if3  |      0 |         0 |
|  5 |         2 |        3 |         2 | if0  |      0 |         0 |
|  6 |         2 |        3 |         2 | if1  |      0 |         0 |
|  7 |         2 |        4 |         2 | if2  |      0 |         0 |
|  8 |         2 |        4 |         2 | if3  |      0 |         0 |
+----+-----------+----------+-----------+------+--------+-----------+
8 rows in set (0.00 sec)

DB> DESC device;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | varchar(32) | YES  |     |         |                |
| description | varchar(32) | YES  |     |         |                |
| address     | varchar(20) | YES  |     |         |                |
| status      | tinyint(4)  | YES  |     | 0       |                |
| flag        | tinyint(4)  | YES  |     | 1       |                |
| order_seq   | int(11)     | YES  |     | 0       |                |
| data_server | varchar(20) | YES  |     |         |                |
+-------------+-------------+------+-----+---------+----------------+

DB> DESC device_group;
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| id              | int(11)     | NO   | PRI | NULL    | auto_increment |
| device_id       | int(11)     | YES  |     | 0       |                |
| name            | varchar(32) | YES  |     |         |                |
| description     | varchar(32) | YES  |     |         |                |
| interface_type  | tinyint(4)  | YES  |     | 0       |                |
| status          | tinyint(4)  | YES  |     | 0       |                |
| order_seq       | tinyint(4)  | YES  |     | 0       |                |
| num_mm_session  | int(11)     | YES  |     | 0       |                |
| num_pdp_session | int(11)     | YES  |     | 0       |                |
| num_session     | int(11)     | YES  |     | 0       |                |
+-----------------+-------------+------+-----+---------+----------------+

DB> DESC device_interface;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| device_id | int(11)     | YES  |     | 0       |                |
| group_id  | int(11)     | YES  |     | 0       |                |
| link_type | tinyint(4)  | YES  |     | 0       |                |
| name      | varchar(10) | YES  |     |         |                |
| status    | tinyint(4)  | YES  |     | 0       |                |
| order_seq | tinyint(4)  | YES  |     | 0       |                |
+-----------+-------------+------+-----+---------+----------------+

它们的关系结构是，device_interface的上级记录是device_group，device_group的上级记录是device。
device的id就是device_group和device_interface中的device_id，device_group的id就是device_interface中的group_id。
我需要一个查询把三个表级联起来用来作为一个树形控件的数据

查询需要生成字段是：id，parentid，name，device_type，address，interface_type，link_type，description。

原来三个表的每一条记录在查询里都有一条记录。
id这个字段除了device这个表使用原始值以外，其它两个表的id都以八进制1-255的方式包含父级表的id，例如：如果是group的记录，id原来是1，device_id原来是1，那么新的id是257（0x100 + 0x1），如果是interface的记录，id原来是1，group_id原来是1（所以device_id是1），那么新的id是65793（0x10000+0x100+0x1)。
parentid则是上级记录新的id。
device_type是一个新的字段，如果记录来自device则字段值是'device'，如果记录来自device_group，字段值是'group'，如果记录来自devcie_interface，字段值是'interface‘。
其它字段如果该记录没有，则为null。
请问该查询的SQL语句应该如何写？

[道玄希言]

根据你的示例数据，你的期望结果值是什么？

[代码誊写工]

引用 1 楼 yangb0803 的回复:

根据你的示例数据，你的期望结果值是什么？

比如，我希望得到的数据是：

+--------+-----------+------------------+-------------+-----------------+----------------+-----------+-------------+
|     id |  parentid | name             | device_type |  address        | interface_type | link_type | description |
+--------+-----------+------------------+-------------+-----------------+----------------+-----------+-------------+
|      1 |      null |   192_168_200_90 |      device |  192.168.200.90 |                |           |     vmprobe |
|      2 |      null |  192_168_200_100 |      device |  192.168.200.90 |                |           |     vmprobe |
|    257 |         1 |             if32 |       group |                 |             0  |           |             |
|    258 |         1 |             if33 |       group |                 |             0  |           |             |
|    513 |         2 |             if32 |       group |                 |             0  |           |             |
|    514 |         2 |             if33 |       group |                 |             0  |           |             |
|  65793 |       257 |              if0 |   interface |                 |                |         2 |             |
|  65794 |       257 |              if1 |   interface |                 |                |         2 |             |
|  66051 |       258 |              if2 |   interface |                 |                |         2 |             |
|  66052 |       258 |              if3 |   interface |                 |                |         2 |             |
| 131333 |       513 |              if0 |   interface |                 |                |         2 |             |
| 131334 |       513 |              if1 |   interface |                 |                |         2 |             |
| 131591 |       514 |              if2 |   interface |                 |                |         2 |             |
| 131592 |       514 |              if3 |   interface |                 |                |         2 |             |
+--------+-----------+------------------+-------------+-----------------+----------------+-----------+-------------+

[道玄希言]

id原来是1，device_id原来是1，那么新的id是257 可以理解 如果 id原来是3，device_id原来是2，那么新的id是513 ， 貌似不符合 （0x200 + 0x3）这个算法吧。。。 凑了下，但是结果跟你的对不上， 仅提供个思路： select id, '' as parentid, name, 'device' as device_type, address, '' as interface_type, '' as link_type, description from device union all select (256*device_id) + id, device_id as parentid, name, 'group' as device_type, '' as address, interface_type, '' as link_type, description from device_group union all select ((65536*t1.device_id) + t2.id + t1.id) as id, t2.id as parentid, name, 'interface' as device_type, '' as address, '' as interface_type, link_type, '' as description from device_interface t1 inner join (select ((256*device_id) + id) as id, id as tid, device_id as parentid from device_group) as t2 on t1.group_id = t2.tid

[代码誊写工]

引用 3 楼 yangb0803 的回复:

id原来是1，device_id原来是1，那么新的id是257 可以理解 如果 id原来是3，device_id原来是2，那么新的id是513 ， 貌似不符合 （0x200 + 0x3）这个算法吧。。。

我当时急忙心算的，错了，多谢指正。

引用 3 楼 yangb0803 的回复:

凑了下，但是结果跟你的对不上， 仅提供个思路： select id, '' as parentid, name, 'device' as device_type, address, '' as interface_type, '' as link_type, description from device union all select (256*device_id) + id, device_id as parentid, name, 'group' as device_type, '' as address, interface_type, '' as link_type, description from device_group union all select ((65536*t1.device_id) + t2.id + t1.id) as id, t2.id as parentid, name, 'interface' as device_type, '' as address, '' as interface_type, link_type, '' as description from device_interface t1 inner join (select ((256*device_id) + id) as id, id as tid, device_id as parentid from device_group) as t2 on t1.group_id = t2.tid

思路清楚了，多谢。