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

MySql 码拜 6年前 (2016-02-12) 533次浏览
讨教各位一个查询。
有三个表,分别是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语句应该怎么样写?

解决方案

80

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

CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明讨教一个三级父子关联查询的SQL语句?
喜欢 (0)
[1034331897@qq.com]
分享 (0)