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

码拜

9年 ago

讨教各位一个查询。
有三个表，分别是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语句应该怎么样写？

解决方案

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