oracle中的with语句子查询-CSDN博客

本文链接：https://blog.csdn.net/cxws110/article/details/86527196

本文介绍了一段复杂的SQL查询语句，该语句从多个表中联接数据，包括成本数据、保险基本信息和运行历史记录。通过使用MyBatis框架的动态SQL特性，展示了如何根据不同的条件过滤数据，如日期范围、责任人等，同时提供了一个汇总查询的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

with a as(
select t.ID,
t.F_FYGSBM,
t.F_XMBMC,
t.F_GSXMK,
t.F_FYXZ,
t.F_JE,
t.F_JBR,
t.F_RQ,
t.F_ZJFTR,
t.F_ZDBXDLR,
t.f_fylxmc,
t.f_bxlsh,
to_char(t.f_ftmx),
t.f_ftje,
b.f_bxsqrq as FRq
from w_w_fm_day_cost t, w_fm_bxjbxx b, bpm_pro_run_his r
where 1 = 1
and b.f_bxlsh = t.f_bxlsh
and t.f_bxlsh is not null
and b.id = r.businesskey
and r.status = 2
<if test="@Ognl@isNotEmpty(id)"> AND ID =#{id} </if>
   <if test="@Ognl@isNotEmpty(FXmfzr)"> AND t.F_XMFZR LIKE #{FXmfzr} </if>
   <if test="@Ognl@isNotEmpty(FGsxmk)"> AND t.F_GSXMK LIKE #{FGsxmk} </if>
   <if test="@Ognl@isNotEmpty(FFylxmc)"> AND t.F_FYLXMC LIKE #{FFylxmc} </if>
   <if test="@Ognl@isNotEmpty(FDqdlrid)"> AND t.F_DQDLRID = #{FDqdlrid} </if>
   <if test="@Ognl@isNotEmpty(FFygsbm)"> AND (t.F_FYGSBM LIKE #{FFygsbm,jdbcType=VARCHAR} or t.F_DQDLRID = #{FDqdlrid,jdbcType=VARCHAR} ) </if>
   <if test="@Ognl@isNotEmpty(FJbr)"> AND t.F_JBR LIKE #{FJbr} </if>
<if test="@Ognl@isNotEmpty(beginFRq)"> AND b.f_bxsqrq >=#{beginFRq,jdbcType=DATE} </if>
   <if test="@Ognl@isNotEmpty(endFRq)"> AND b.f_bxsqrq <=#{endFRq,jdbcType=DATE} </if>
   <if test="@Ognl@isNotEmpty(FFyxz)"> AND F_FYXZ LIKE #{FFyxz} </if>
)select * from (
select * from a
union
select null,'合计',null,null,null,to_char(sum(to_number(b.f_je))),null,null,null,null,null,null,null,null,null
from a b
)