XML配置代码如下:
union all查询的时候注意的有两点
1:union al前后两个子部分的查询的字段要一致
2:注意给子部分取别名
<select id="findFarmByUserAndAuthor" resultMap="FarmListMap"
parameterType="java.lang.String">select t3.farmid farmid,t3.strname strname,t3.state state,t3.type type,t3.strPhoneId strPhoneId from (
select t1.farmid,t1.strname,t1.state,t1.type,t1.strPhoneId from (
select f.id farmid,f.strName strname,f.state state,f.type type,
(select
t.strPhoneId from termuser t where
t.id=f.iUserId)strphoneid
from farm f where f.iUserId =#{userId}) as t1
UNION ALL
select t2.farmid,t2.strname,t2.state,t2.type,t2.strPhoneId from (
select f.id farmid,f.strName strname,f.state state,f.type type,(select
t.strPhoneId from termuser t where
t.id=f.iUserId)strphoneid from author a
LEFT JOIN farm f ON f.ID = a.FARM_ID
where a.AUTHORED_ID = #{userId}) as t2)as t3 where
<if test="strName != null and '' != strName">
t3.strname like CONCAT(CONCAT('%', #{strName}), '%')
</if>
limit #{curSize},#{pageSize}
</select>