一条集多表查询、字段与字段拼接、合并每张表共同字段、新增列并赋值的SQL

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