union的用法，记录工作中的sql

查询语句与count语句

最新推荐文章于 2024-08-20 01:40:19 发布

原创最新推荐文章于 2024-08-20 01:40:19 发布 · 198 阅读

0 ·

CC 4.0 BY-SA版权

Java 专栏收录该内容

69 篇文章

订阅专栏

博客提及了查询语句和count语句，二者在信息技术领域的数据库操作中较为常见，查询语句用于获取数据，count语句可统计数据数量。

查询语句

<select id="findAll" resultMap="BaseResultMap">
		select * from (select rownum rownum_rn, a.* from(SELECT * from (
		
		select 
				d.ID , d.C_DISTRICTBCDID , d.C_ADDRESSID , d.C_CHANNELNUM , d.C_HARDWAREVER , d.C_SOFTWAREVER , d.C_FIXIP , d.C_LASTCOMTIME , d.C_SOFTUPDATEDATE , d.C_INSTALLDATE , d.C_DESP , d.C_DEVTYPE , d.NBDEVICEID , d.NBDEVICENAME , mt.EPU_LOCAL, mt.EPU_PROVINCE,mt.EPU_CITY,mt.EPU_DISTRICT,mt.EPU_COURT 
			FROM T_DEVICEINFO d 
			LEFT JOIN T_METERBOX mt on d.C_DistrictBCDId = mt.C_DistrictBCDId and d.C_AddressId = mt.C_AddressId and d.C_ChannelNum = mt.C_CHANNELNUM 
			WHERE d.C_DEVTYPE = 0
			
			UNION
			
			select 
				d.ID , d.C_DISTRICTBCDID , d.C_ADDRESSID , d.C_CHANNELNUM , d.C_HARDWAREVER , d.C_SOFTWAREVER , d.C_FIXIP , d.C_LASTCOMTIME , d.C_SOFTUPDATEDATE , d.C_INSTALLDATE , d.C_DESP , d.C_DEVTYPE , d.NBDEVICEID , d.NBDEVICENAME , oc.EPU_LOCAL, oc.EPU_PROVINCE,oc.EPU_CITY,oc.EPU_DISTRICT,oc.EPU_COURT 
			FROM T_DEVICEINFO d 
			LEFT JOIN T_OUTGOINGCABINET oc ON d.C_DistrictBCDId = oc.C_DistrictBCDId AND d.C_AddressId = oc.C_AddressId AND d.C_ChannelNum = oc.C_CHANNELNUM 
			WHERE d.C_DEVTYPE = 1
			
			UNION
			
			select 
				d.ID , d.C_DISTRICTBCDID , d.C_ADDRESSID , d.C_CHANNELNUM , d.C_HARDWAREVER , d.C_SOFTWAREVER , d.C_FIXIP , d.C_LASTCOMTIME , d.C_SOFTUPDATEDATE , d.C_INSTALLDATE , d.C_DESP , d.C_DEVTYPE , d.NBDEVICEID , d.NBDEVICENAME , ts.EPU_LOCAL, ts.EPU_PROVINCE,ts.EPU_CITY,ts.EPU_DISTRICT,ts.EPU_COURT 
			FROM T_DEVICEINFO d 
			LEFT JOIN T_SUBSTAIN ts ON d.C_DistrictBCDId = ts.C_DistrictBCDId AND d.C_AddressId = ts.C_AddressId 
			WHERE d.C_DEVTYPE = 2
		
		) res where 1=1
		<if test="cDistrictbcdid != '' and cDistrictbcdid != null">
			<![CDATA[ and C_DistrictBCDId LIKE concat(concat('%',#{cDistrictbcdid,jdbcType=VARCHAR}),'%')]]>
		</if>
		<if test="cAddressid != '' and cAddressid != null">
			<![CDATA[ and C_AddressId LIKE concat(concat('%',#{cAddressid,jdbcType=VARCHAR}),'%')]]>
		</if>
		<if test="cInstalldateBegin != '' and cInstalldateBegin != null">
			and C_InstallDate &gt;=#{cInstalldateBegin,jdbcType=VARCHAR}
		</if>
		<if test="cInstalldateEnd != '' and cInstalldateEnd != null">
			and C_InstallDate &lt;=#{cInstalldateEnd,jdbcType=VARCHAR}
		</if>
		<if test="epuProvince != '' and epuProvince != null">
			and EPU_PROVINCE =#{epuProvince,jdbcType=VARCHAR}
		</if>
		<if test="epuCity != '' and epuCity != null">
			and EPU_CITY =#{epuCity,jdbcType=VARCHAR}
		</if>
		<if test="epuDistrict != '' and epuDistrict != null">
			and EPU_DISTRICT =#{epuDistrict,jdbcType=VARCHAR}
		</if>
		<if test="epuCourt != '' and epuCourt != null">
			and EPU_COURT =#{epuCourt,jdbcType=VARCHAR}
		</if>
		<if test="devType != null">
			and C_DEVTYPE =#{devType,jdbcType=INTEGER}
		</if>
		order by C_InstallDate DESC
<!-- 		order by  EPU_PROVINCE,EPU_CITY,EPU_DISTRICT,EPU_COURT asc -->
		) a)
		<include refid="limit_sql" />
	</select>

count语句

<select id="findCount" resultMap="BaseResultMap">
	
	select count(*) 
		FROM
		
		(
		select 
				d.ID , d.C_DISTRICTBCDID , d.C_ADDRESSID , d.C_CHANNELNUM , d.C_HARDWAREVER , d.C_SOFTWAREVER , d.C_FIXIP , d.C_LASTCOMTIME , d.C_SOFTUPDATEDATE , d.C_INSTALLDATE , d.C_DESP , d.C_DEVTYPE , d.NBDEVICEID , d.NBDEVICENAME , mt.EPU_LOCAL, mt.EPU_PROVINCE,mt.EPU_CITY,mt.EPU_DISTRICT,mt.EPU_COURT 
			FROM T_DEVICEINFO d 
			LEFT JOIN T_METERBOX mt on d.C_DistrictBCDId = mt.C_DistrictBCDId and d.C_AddressId = mt.C_AddressId and d.C_ChannelNum = mt.C_CHANNELNUM 
			WHERE d.C_DEVTYPE = 0
			
			UNION
			
			select 
				d.ID , d.C_DISTRICTBCDID , d.C_ADDRESSID , d.C_CHANNELNUM , d.C_HARDWAREVER , d.C_SOFTWAREVER , d.C_FIXIP , d.C_LASTCOMTIME , d.C_SOFTUPDATEDATE , d.C_INSTALLDATE , d.C_DESP , d.C_DEVTYPE , d.NBDEVICEID , d.NBDEVICENAME , oc.EPU_LOCAL, oc.EPU_PROVINCE,oc.EPU_CITY,oc.EPU_DISTRICT,oc.EPU_COURT 
			FROM T_DEVICEINFO d 
			LEFT JOIN T_OUTGOINGCABINET oc ON d.C_DistrictBCDId = oc.C_DistrictBCDId AND d.C_AddressId = oc.C_AddressId AND d.C_ChannelNum = oc.C_CHANNELNUM 
			WHERE d.C_DEVTYPE = 1
			
			UNION
			
			select 
				d.ID , d.C_DISTRICTBCDID , d.C_ADDRESSID , d.C_CHANNELNUM , d.C_HARDWAREVER , d.C_SOFTWAREVER , d.C_FIXIP , d.C_LASTCOMTIME , d.C_SOFTUPDATEDATE , d.C_INSTALLDATE , d.C_DESP , d.C_DEVTYPE , d.NBDEVICEID , d.NBDEVICENAME , ts.EPU_LOCAL, ts.EPU_PROVINCE,ts.EPU_CITY,ts.EPU_DISTRICT,ts.EPU_COURT 
			FROM T_DEVICEINFO d 
			LEFT JOIN T_SUBSTAIN ts ON d.C_DistrictBCDId = ts.C_DistrictBCDId AND d.C_AddressId = ts.C_AddressId 
			WHERE d.C_DEVTYPE = 2
		) a
		
		where 1=1
		
		<if test="cDistrictbcdid != '' and cDistrictbcdid != null">
			<![CDATA[ and a.C_DistrictBCDId LIKE concat(concat('%',#{cDistrictbcdid,jdbcType=VARCHAR}),'%')]]>
		</if>
		
		<if test="cAddressid != '' and cAddressid != null">
			<![CDATA[ and a.C_AddressId LIKE concat(concat('%',#{cAddressid,jdbcType=VARCHAR}),'%')]]>
		</if>
		<if test="cInstalldateBegin != '' and cInstalldateBegin != null">
			<![CDATA[ and a.C_InstallDate >= #{cInstalldateBegin,jdbcType=VARCHAR}]]>
		</if>
		<if test="cInstalldateEnd != '' and cInstalldateEnd != null">
			<![CDATA[ and da.C_InstallDate <= #{cInstalldateEnd,jdbcType=VARCHAR}]]>
		</if>
		<if test="epuProvince != '' and epuProvince != null">
			<![CDATA[ and a.EPU_PROVINCE =#{epuProvince,jdbcType=VARCHAR}]]>
		</if>
		<if test="epuCity != '' and epuCity != null">
			<![CDATA[and a.EPU_CITY =#{epuCity,jdbcType=VARCHAR}]]>
		</if>
		<if test="epuDistrict != '' and epuDistrict != null">
			<![CDATA[and a.EPU_DISTRICT =#{epuDistrict,jdbcType=VARCHAR}]]>
		</if>
		<if test="epuCourt != '' and epuCourt != null">
			<![CDATA[and a.EPU_COURT =#{epuCourt,jdbcType=VARCHAR}]]>
		</if>
		<if test="devType != null">
			and a.C_DEVTYPE =#{devType,jdbcType=INTEGER}
		</if>
	</select>