一条集多表查询、字段与字段拼接、合并每张表共同字段、新增列并赋值的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'
      )