讨教各位一个查询。
有三个表，分别是device，device_group，device_interface，它们的内容和结构如下：
有三个表，分别是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
假如
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语句？！
