定义一个SQL片段。unionListView , 第一个<include refid="outTable"/> 为第一张表 第二个<include refid="inTable"/> 为第二张表
<sql id="unionListView">
(<include refid="outTable"/>
UNION ALL
<include refid="inTable"/>)
</sql>
定义outTable 代码片段,第一张表没有CREATEUSER ,需要使用null as CREATEUSER 填充字段
<sql id="outTable">
SELECT
'2' as CHOOSE,
ID AS ID,
to_Date(CREATETIME, 'yyyy-mm-dd hh24:mi:ss') as CREATETIME,
null as CREATEUSER,
DEPARTMENT as DEPARTMENT,
VEHICLENUMBER as VEHICLENUMBER
FROM
TABLE_OUT
</sql>
定义inTable 代码片段,其中PLATENO 字段名与第一张表不同,需要重新命名才能合并
<sql id="inTable">
SELECT
'2' as CHOOSE,
ID AS ID,
to_Date(CREATETIME, 'yyyy-mm-dd hh24:mi:ss') as CREATETIME,
CREATEUSER as CREATEUSER,
DEPARTMENT as DEPARTMENT,
PLATENO as VEHICLENUMBER
FROM
TABLE_IN
</sql>
定义查询条件,起名<sql id="whereList">
<sql id="whereList">
<where>
<if test="plateNumber != null and plateNumber != ''">
and VEHICLENUMBER like concat(concat('%',#{plateNumber}),'%')
</if>
</where>
</sql>
将<sql id="unionListView"> 做为一个结果集和查询条件放在一起,然后手动分页
<select id="getList" resultType="com.xxx" parameterType="com.xxx">
select * from
(select temp.*,rownum row_id from(
(select * from
<include refid="unionListView"/>
<include refid="whereList"/>
order by CREATETIME desc)
)temp where rownum <= #{end})
where row_id > #{begin}
</select>
完整的语句为:
select * from
(select temp.*,rownum row_id from(
(select * from
(SELECT
'2' as CHOOSE,
ID AS ID,
to_Date(CREATETIME, 'yyyy-mm-dd hh24:mi:ss') as CREATETIME,
null as CREATEUSER,
DEPARTMENT as DEPARTMENT,
VEHICLENUMBER as VEHICLENUMBER
FROM
TABLE_OUT
UNION ALL
SELECT
'2' as CHOOSE,
ID AS ID,
to_Date(CREATETIME, 'yyyy-mm-dd hh24:mi:ss') as CREATETIME,
CREATEUSER as CREATEUSER,
DEPARTMENT as DEPARTMENT,
PLATENO as VEHICLENUMBER
FROM
TABLE_IN)
where VEHICLENUMBER like concat(concat('%','苏A00001'),'%')
order by CREATETIME desc)
)temp where rownum < 10)
where row_id > 0
|