關於Oracle 合并欄位

標籤：har   last   select   group by   cdp   lis   var   res   where   

<select id="I0203F0004SQL001"
parameterType="com.dsunsoft.cqhzz.sql.dto.I0203.I0203F0004SQL001DbcIU001"
resultType="com.dsunsoft.cqhzz.sql.dto.I0203.I0203F0004SQL001DbcOU001">
SELECT
t1.USER_ID AS userId,
t1.USER_NM AS userNm,
t2.EMP_NM AS empNm,
t1.PWD AS pwd,
t1.TEL AS tel,
t1.E_MAIL AS eMail,
t1.WE_CHAT AS weChat,
t1.EMP_ID AS empId,
t1.CREATED_BY AS createdBy,
t1.CREATED_DATE AS createdDate,
t1.LAST_MODIFIED_BY AS lastModifiedBy,
t1.LAST_MODIFIED_DATE AS lastModifiedDate,
t1.IS_DEL AS isDel,
t1.AVATAR AS avatar,
t3.roleNms AS roleNms,
t4.rvSegNms AS rvSegNms,
t5.ADDVNM AS addvnm,
t5.ADDVCD_PATH AS addvcdPath,
t5.ADDVNM_PATH AS addvnmPath
FROM
TB_WR_SYS_USER t1
LEFT JOIN
TB_WR_SYS_EMP t2 ON t1.EMP_ID = t2.EMP_ID
LEFT JOIN
(SELECT t11.USER_ID,
LISTAGG(t21.ROLE_NM, ‘,‘) WITHIN GROUP (ORDER BY t11.ROLE_ID) AS roleNms
FROM TB_WR_SYS_USER_ROLE t11,TB_WR_SYS_ROLE t21
WHERE t11.ROLE_ID = t21.ROLE_ID
GROUP BY t11.USER_ID) t3 ON t1.USER_ID = t3.USER_ID
LEFT JOIN
(SELECT t11.EMP_ID,
LISTAGG(t21.RV_SEG_NM, ‘,‘) WITHIN GROUP (ORDER BY t21.ORDER_INDEX) AS rvSegNms
FROM TB_WR_RPS_EMP t11,TB_WR_RV_SEG t21
WHERE t11.RV_SEG_ID = t21.RV_SEG_ID
GROUP BY t11.EMP_ID) t4 ON t1.EMP_ID = t4.EMP_ID
LEFT JOIN
TB_ADDV_B t5 ON t2.ADDVCD = t5.ADDVCD
<where>
(t1.IS_DEL = 0 OR t1.IS_DEL IS NULL)
<if test="userId != null">
AND t1.USER_ID = #{userId, jdbcType=VARCHAR}
</if>
<if test="userNm != null and userNm != ‘‘">
AND t1.USER_NM LIKE ‘%${userNm}%‘
</if>
<if test="empId != null and empId != ‘‘">
AND t1.EMP_ID = #{empId, jdbcType=VARCHAR}
</if>
<if test="tel != null and tel !=‘‘">
AND t1.TEL = #{tel, jdbcType=VARCHAR}
</if>
<if test="addvcdPath != null and addvcdPath !=‘‘">
AND t5.ADDVCD_PATH LIKE ‘%${addvcdPath}%‘
</if>
</where>
</select>

關於Oracle 合并欄位