优雅sql

<select id="getContainerStatisticalList" parameterType="com.ruoyi.project.analysis.vo.AnalysisVo" resultType="com.ruoyi.project.analysis.vo.ContainerStatisticalItem">
SELECT
a.CT_VSLVOY_ID as ctVslvoyId,
a.DEPTNAME,
a.CVSLNAME,
a.VOYAGE,
a.LINENAME,
( CASE WHEN a.IF_ID = 1 THEN '外' WHEN a.IF_ID = 2 THEN '内' END ) AS ifId,
a.SAILINGDATE,
( CASE WHEN a.IO_ID = 1 THEN '出' WHEN a.IO_ID = 2 THEN '进' END ) AS ioId,
c.AMOUNT as totalAmount,
(CASE
WHEN d.FREMODE = 2 THEN
(SELECT wm_concat ( CNAME ) FROM CT_SHIPPINGAGENCY WHERE SACODE IN ( SELECT regexp_substr( d.OWNERCODE, '[^/]+', 1, LEVEL ) FROM dual CONNECT BY LEVEL &lt;= LENGTH( d.OWNERCODE ) - LENGTH( REPLACE ( d.OWNERCODE, '/' ) ) + 1 ) )
ELSE
(SELECT wm_concat ( CNAME ) FROM CT_CARRIER WHERE CRCODE IN ( SELECT regexp_substr( d.OWNERCODE, '[^/]+', 1, LEVEL ) FROM dual CONNECT BY LEVEL &lt;= LENGTH( d.OWNERCODE ) - LENGTH( REPLACE ( d.OWNERCODE, '/' ) ) + 1 ) )
END) AS agencyname,
c.PACNAME AS pacname,
c.BUSINESS_RECORDED_TIME as businessRecordedTime,
c.FINANCE_RECORDED_TIME as financeRecordedTime
FROM
CT_VSLVOY a
LEFT JOIN CT_FRETITLE c
ON c.CT_VSLVOY_ID = a.CT_VSLVOY_ID
AND c.CHECK_ID = 'Y'
<if test="wtrname != null and wtrname != ''">
and c.PACNAME like concat(concat('%', #{wtrname}), '%')
</if>
LEFT JOIN CT_ACCOUNT d ON d.CT_ACCOUNT_ID = c.CT_ACCOUNT_ID
WHERE
a.SAILINGDATE BETWEEN #{alsStartDate} AND #{alsEndDate}
AND a.VESSELTYPE = '集装箱船'
<if test="params.ioId != null and params.ioId != ''">AND a.io_id = #{params.ioId}</if>
<if test="ioId != null and ioId != ''">AND a.io_id = #{ioId}</if>
<if test="ifId != null and ifId != ''">AND a.if_id = #{ifId}</if>
<if test="agencyname != null and agencyname != ''">AND a.agencyname like concat(concat('%', #{agencyname}), '%')</if>
<if test="linename != null and linename != ''">AND a.linename like concat(concat('%', #{linename}), '%')</if>
<if test="zhwchm != null and zhwchm != ''"> and a.cvslname like concat(concat('%', #{zhwchm}), '%')</if>
<if test="wtrname != null and wtrname != ''">
and EXISTS ( SELECT 1 FROM CT_FRETITLE f WHERE f.CT_VSLVOY_ID = a.CT_VSLVOY_ID AND f.PACNAME like concat(concat('%', #{wtrname}), '%') )
</if>
<if test="finishId != null and finishId != ''"> and nvl(a.FINISH_ID,'N') = #{finishId} </if>
<if test="accFini != null and accFini != ''"> and nvl(a.ACC_FINI,'0') = #{accFini} </if>
<if test="checkId != null and checkId != ''">
and EXISTS ( SELECT 1 FROM CT_FRETITLE f WHERE f.CT_VSLVOY_ID = a.CT_VSLVOY_ID AND nvl(f.CHECK_ID,'N') = #{checkId} )
</if>
ORDER BY a.DEPTNAME, a.SAILINGDATE DESC
</select>