mapper语句的一些问题，union连表查询和mapper中根据条件不同采用不同语句的查询问题
2024-09-02 14:37:09
根据业务要求，不同表查出来的内容天需要一起展示出来，并且还有分页之类的，不同表查询字段也不完全相同，这样就有一个问题，不同表如何接合在一起，不同字段怎么办？
这个问题就需要用到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>
如此这般，得以解决，推测有可能是一个select标签下只能运行一个主select语句。
最新文章
- 【jdbcTemplate】使用jdbcTemplate查询的三种回调
- Yarn中如何生成状态机图
- sigleSchool 存储过程例1
- There is an error in invoking javac. A full JDK (not just JRE) is required
- 个人笔记之json实现模糊查询
- 2017-2018-1 1623 bug终结者 冲刺006
- Maven原型骨架及常见问题
- java过滤emoji表情
- 1.Sed | Awk | Grep | Find
- 《设计模式》学习&理解&总结
- php -- 类对象调用静态方法
- RPC的基础：调研EOS插件http_plugin
- STF环境搭建（ubuntu）
- WPF学习笔记（2）：准确定位弹出窗
- Markdown 编辑器
- iOS必学技－cocoapods
- php7 & lua 压测对比
- mysql基本语句集合
- Object-c中block需要注意的几点问题
- mysql explain extended 查看 执行计划