SQL如下:
SELECT
ATableID AS deviceId,AIP AS terminalIp,( aName+ '(' + AIP + ')' ) AS name,'1' AS devParentId
FROM
ATable
WHERE
is_active = '1'
UNION
(
SELECT
BTableID AS deviceId,BIP AS terminalIp,( bName+ '(' + BIP + ')' ) AS name,'2' AS devParentId
FROM
BTable
WHERE
is_active = '1'
)
UNION
(
SELECT
CTableID AS deviceId,CIP AS terminalIp,( cName+ '(' + CIP + ')' ) AS name,'3' AS devParentId
FROM
CTable
WHERE
is_active = '1'
)
分析:
字段拼接:( NAME + '(' + ControlIP + ')' ) AS NAME
合并多表: 使用了UNION,参考这篇博客。
新增列并赋值: :'3' AS devParentId