mapper语句的一些问题，union连表查询和mapper中根据条件不同采用不同语句的查询问题

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

根据业务要求，不同表查出来的内容天需要一起展示出来，并且还有分页之类的，不同表查询字段也不完全相同，这样就有一个问题，不同表如何接合在一起，不同字段怎么办？

这个问题就需要用到union联合查询，并将不同字段采用共有字段去展示，在前台显示则需要根据其他字段去判断展示，就达到目的了。但是由于本业务比较复杂，需要根据传入的不同值，分别进行不同查询，有联合的，非联合的等，在一个<select></select>标签下搞不定，代码如下：

<select id="userSelectMessageList" parameterType="map" resultMap="BaseResultMap">

	<if test="messageType!=null and messageType=0">

	SELECT <include refid="User_Base_Column_List"/>,t.deviceNum `number`,a.`user_name` `name` FROM t_message m INNER JOIN

		(SELECT COUNT(d.`device_id`) deviceNum,d.`order_id`,o.`account_id` FROM `xxx_order_device` d INNER JOIN xxx_order o ON d.`order_id`=o.`id` WHERE d.`del_flag`=0 AND o.`del_flag`=0 GROUP BY d.`order_id`, o.`account_id`) t

	ON m.`value1`=t.`order_id` INNER JOIN u_account a ON t.`account_id`=a.`id` WHERE info_type=12

	AND m.`company_id`=#{companyId} AND m.`product_id`=#{productId}

			<if test="content!=null">

				AND (d.`device_name` LIKE #{content} OR d.`model` LIKE #{content})

			</if>

	UNION

	SELECT <include refid="User_Base_Column_List"/>, d.model `number`, d.device_name `name` FROM t_message m INNER JOIN xxx_device_repair gdr ON m.value1=gdr.id INNER JOIN d_device d ON d.id=gdr.device_id WHERE info_type=16

	AND m.`company_id`=#{companyId} AND m.`product_id`=#{productId}

			<if test="content!=null">

				AND (d.`device_name` LIKE #{content} OR d.`model` LIKE #{content})

			</if>

	</if>

	<if test="messageType!=null and messageType=12">

	SELECT <include refid="User_Base_Column_List"/>,t.deviceNum `number`,a.`user_name` `name` FROM t_message m INNER JOIN

		(SELECT COUNT(d.`device_id`) deviceNum,d.`order_id`,o.`account_id` FROM `xxx_order_device` d INNER JOIN xxx_order o ON d.`order_id`=o.`id` WHERE d.`del_flag`=0 AND o.`del_flag`=0 GROUP BY d.`order_id`, o.`account_id`) t

	ON m.`value1`=t.`order_id` INNER JOIN u_account a ON t.`account_id`=a.`id` WHERE info_type=12

	AND m.`company_id`=#{companyId} AND m.`product_id`=#{productId}

			<if test="content!=null">

				AND (d.`device_name` LIKE #{content} OR d.`model` LIKE #{content})

			</if>

	</if>

	<if test="messageType!=null and messageType=16">

	SELECT <include refid="User_Base_Column_List"/>, d.model `number`, d.device_name `name` FROM t_message m INNER JOIN xxx_device_repair gdr ON m.value1=gdr.id INNER JOIN d_device d ON d.id=gdr.device_id WHERE info_type=16

	AND m.`company_id`=#{companyId} AND m.`product_id`=#{productId}

			<if test="content!=null">

				AND (d.`device_name` LIKE #{content} OR d.`model` LIKE #{content})

			</if>

	</if>

  </select>

查询的结果总是报错，最主要是控制台打印出来的语句全有，并且中间没有union 关键字连接，

最后不得已只能将其拆分，然后在service层中根据messageType的不同来控制不同查询。

代码如下：

   <sql id="User_Base_Column_List">

	m.id, m.uid, m.user_id, m.family_id,

	m.device_id, m.info_type, m.type, m.time, m.value1, m.product_id, m.company_id ,

	m.text, m.create_time, m.update_time, m.del_flag

  </sql>

  <select id="userSelectMessageList0" parameterType="map" resultMap="BaseResultMap">

	SELECT <include refid="User_Base_Column_List"/>,t.deviceNum `number`,a.`user_name` `name` FROM t_message m INNER JOIN

		(SELECT COUNT(d.`device_id`) deviceNum,d.`order_id`,o.`account_id` FROM `xxx_order_device` d INNER JOIN xxx_order o ON d.`order_id`=o.`id` WHERE d.`del_flag`=0 AND o.`del_flag`=0 GROUP BY d.`order_id`, o.`account_id`) t

	ON m.`value1`=t.`order_id` INNER JOIN u_account a ON t.`account_id`=a.`id` WHERE info_type=12

	AND m.`company_id`=#{companyId} AND m.`product_id`=#{productId}

			<if test="content!=null">

				AND (d.`device_name` LIKE #{content} OR d.`model` LIKE #{content})

			</if>

	UNION

	SELECT <include refid="User_Base_Column_List"/>, d.model `number`, d.device_name `name` FROM t_message m INNER JOIN xxx_device_repair gdr ON m.value1=gdr.id INNER JOIN d_device d ON d.id=gdr.device_id WHERE info_type=16

	AND m.`company_id`=#{companyId} AND m.`product_id`=#{productId}

			<if test="content!=null">

				AND (d.`device_name` LIKE #{content} OR d.`model` LIKE #{content})

			</if>

	</select>

  <select id="userSelectMessageList12" parameterType="map" resultMap="BaseResultMap">

  SELECT <include refid="User_Base_Column_List"/>,t.deviceNum `number`,a.`user_name` `name` FROM t_message m INNER JOIN

		(SELECT COUNT(d.`device_id`) deviceNum,d.`order_id`,o.`account_id` FROM `xxx_order_device` d INNER JOIN xxx_order o ON d.`order_id`=o.`id` WHERE d.`del_flag`=0 AND o.`del_flag`=0 GROUP BY d.`order_id`, o.`account_id`) t

	ON m.`value1`=t.`order_id` INNER JOIN u_account a ON t.`account_id`=a.`id` WHERE info_type=12

	AND m.`company_id`=#{companyId} AND m.`product_id`=#{productId}

			<if test="content!=null">

				AND (d.`device_name` LIKE #{content} OR d.`model` LIKE #{content})

			</if>

  </select>

	<select id="userSelectMessageList16" parameterType="map" resultMap="BaseResultMap">

  	SELECT <include refid="User_Base_Column_List"/>, d.model `number`, d.device_name `name` FROM t_message m INNER JOIN xxx_device_repair gdr ON m.value1=gdr.id INNER JOIN d_device d ON d.id=gdr.device_id WHERE info_type=16

	AND m.`company_id`=#{companyId} AND m.`product_id`=#{productId}

			<if test="content!=null">

				AND (d.`device_name` LIKE #{content} OR d.`model` LIKE #{content})

			</if>

	</select>