sql语句的字段升序排列及其"NOT IN"、"UNION ALL"的用法

 字段升序排列：

注意：当字段为字符串时。默认为用字符码的编制顺序为默认顺序。

 

	<!-- 所有的楼层 -->
	<select id="findBuildingAndFloorByDistrictAndGroup" resultType="com.xx.xx.xx.xx.MyErpInstrumentGroupFloorRefVO" parameterType="com.xx.xx.xx.xx.MyErpInstrumentGroupFloorRefVO">
				SELECT * FROM ((SELECT
			b.id,
			b.id AS building_id,
			b.building_name NAME,
			b.merchant_district_id pId,
			'building' AS type,
			0 AS checked
		FROM
			x b
		WHERE
		b.del_stauts = 1
		<if test="merchantDistrictId != null and merchantDistrictId.trim() != ''">
		AND b.merchant_district_id = #{merchantDistrictId}
		</if>
		AND b.id NOT IN (
			SELECT
				g.building_id
			FROM
				xx g
			LEFT JOIN xxx eig ON eig.id = g.group_id
			WHERE
				 g.ref_type = 2
			AND g.ref_status = 1
		<if test="instrumentAttribute != null and instrumentAttribute != ''">
			AND eig.instrument_attribute = #{instrumentAttribute}
		</if>
			<if test="groupId != null and groupId.trim() != ''">
				AND g.group_id &lt;&gt; #{groupId}
			</if>
		)
		 )
		UNION ALL
			(SELECT
				f.id,
				f.building_id,
				f.floor NAME,
				f.building_id pId,
				'floor' AS type,
				0 AS checked
			FROM
				xxx f
			WHERE
				f. STATUS = 1
				AND f.building_id IS NOT NULL
				<if test="merchantDistrictId != null and merchantDistrictId.trim() != ''">
				AND f.merchant_district_id = #{merchantDistrictId}
				</if>
			AND f.id NOT IN (
				SELECT
					g.floor_id
				FROM
					x g
			LEFT JOIN xx eig ON eig.id = g.group_id
				WHERE
			 g.ref_type = 1
			 AND g.ref_status = 1
		<if test="instrumentAttribute != null and instrumentAttribute != ''">
			AND eig.instrument_attribute = #{instrumentAttribute}
		</if>
			<if test="groupId != null and groupId.trim() != ''">
				AND g.group_id &lt;&gt; #{groupId}
			</if>
			)
		 			t ORDER BY t.name ASC
	</select>