爱码博阁

查询出所有重复字段的数据

select * from consult_evaluate
where consult_id in (select consult_id from consult_evaluate group by consult_id having count(consult_id) > 1) AND IS_delete = 0 ORDER BY consult_id

ORACLE根据多个字段进行去重

 select
        OP.rn,OP.ID, OP.WARD_CODE, OP.WARD_NAME, OP.PATIENT_ID, OP.MRN, OP.NAME, OP.SEX, OP.AGE, OP.OUT_TIME, OP.OUT_CATEGORY,
        OP.PHONE,OP.UNIT_ID,OP.UNIT_NAME,OP.DEPT_ID,OP.DEPT_NAME,OP.VALID_FLAG, OP.CREATE_TIME, OP.CREATE_BY, OP.UPDATE_TIME,
        OP.UPDATE_BY,OP.STATUS,OP.SMS_SEND_DATE,OP.SMS_SEND_MONTH
        from
        (
            select OP.*,PPM.STATUS,PPM.SMS_SEND_DATE,PPM.SMS_SEND_MONTH,row_number() over (partition by OP.NAME, OP.PHONE order by PPM.SMS_SEND_DATE DESC) rn from OUT_PATIENTS OP
            left join PATIENTS_PAPER_MAP PPM on PPM.MRN=OP.MRN and PPM.valid_flag =1 and PPM.PAPER_ID = 1175
        ) OP
where  OP.SMS_SEND_DATE IS NOT NULL
and OP.rn = 1
order by OP.SMS_SEND_DATE DESC

此例子利用窗口函数row_number根据OP.NAME, OP.PHONE两个字段进行去重

加上条件OP.rn = 1，代表取重复行的第1行

注：若OP.NAME、OP.PHONE为NULL会判定重复

oracle报错解决：ORA-01795: maximum number of expressions in a list

原SQL：当ids长度大于1000个oracle就会抛出该异常

<if test="ids != null and ids.size() > 0">
    AND ID IN
    <foreach item="item" index="index" collection="ids" open="(" separator="," close=")">
        #{item,jdbcType=VARCHAR}
    </foreach>
</if>

改造SQL:

<if test="ids != null and ids.size() > 0">
    AND (ID IN
    <foreach item="item" index="index" collection="ids" open="(" separator="," close=")">
        <if test="(index % 999) == 998">NULL) OR ID IN (</if>#{item,jdbcType=VARCHAR}
    </foreach>)
</if>

新增时去重：

<insert id="insertDiff" keyProperty="id" useGeneratedKeys="true">
    INSERT INTO <include refid="Table_Name" /> (ID, NURSE_SHIFT_ROTATION_INFO_ID, UNIT_ID, UNIT_NAME, START_DATE, END_DATE, VALID_FLAG, CREATE_ID, CREATE_TIME, UPDATE_ID, UPDATE_TIME)
    SELECT #{id}, #{nurseShiftRotationInfoId}, #{unitId}, #{unitName}, #{startDate}, #{endDate}, #{validFlag}, #{createId}, #{createTime}, #{createId}, #{createTime}
    FROM dual
    WHERE NOT EXISTS (
        SELECT 1
        FROM <include refid="Table_Name" />
        WHERE NURSE_SHIFT_ROTATION_INFO_ID = #{nurseShiftRotationInfoId}
          AND END_DATE &gt;= #{startDate}
          AND START_DATE &lt;= #{endDate}
          AND VALID_FLAG = 1
    )
</insert>

批量插入（oracle）：

<insert id="insertBatch" parameterType="java.util.List">
    begin
    <foreach collection="list" item="item" index="index" separator=";">
        insert into <include refid="Table_Name" /> (ID, NURSE_SHIFT_ROTATION_INFO_ID, UNIT_ID, UNIT_NAME, START_DATE, END_DATE, VALID_FLAG, CREATE_ID, CREATE_TIME, UPDATE_ID, UPDATE_TIME)
        values (
        #{item.id},
        #{item.nurseShiftRotationInfoId},
        #{item.unitId},
        #{item.unitName},
        #{item.startDate},
        #{item.endDate},
        #{item.validFlag},
        #{item.createId},
        #{item.createTime},
        #{item.createId},
        #{item.createTime}
        )
    </foreach>
    ;end;
</insert>

批量更新（oracle）：

<update id="updateBatch">
    <foreach collection="list" item="item" index="index" open="begin" close="end;" separator=""> <!-- 这是oracle的写法-->
        update <include refid="Table_Name" />
        <set >
            <if test="item.unitId != null and item.unitId != ''">
                UNIT_ID = #{item.unitId},
            </if>
            <if test="item.unitName != null and item.unitName != ''">
                UNIT_NAME = #{item.unitName},
            </if>
            <if test="item.startDate != null">
                START_DATE = #{item.startDate},
            </if>
            <if test="item.endDate != null">
                END_DATE = #{item.endDate},
            </if>
            <if test="item.updateId != null and item.updateId != ''">
                UPDATE_ID = #{item.updateId},
            </if>
            <if test="item.updateTime != null">
                UPDATE_TIME = #{item.updateTime},
            </if>
        </set>
        where id = #{item.id,jdbcType=VARCHAR};
    </foreach>
</update>

mybatis的collection合并为列表demo

分页时需要分开两步查询，

1、StatisticsAccountResultMap去重查询人员相关

2、StatisticsResultMap从第1步中查询出的人员传入

若无需分页则只需要第2步直接查询会根据非collection字段进行合并list到实体类的scoreList属性

<resultMap id="StatisticsAccountResultMap"
           type="com.bozhong.satisfaction.domain.dto.response.EmployeeSatisfactionStatisticsDataRespDTO">
    <result column="RECEIVE_ACCOUNT_ID" property="receiveAccountId"/>
    <result column="RECEIVE_ACCOUNT_JOB_NUMBER" property="receiveAccountJobNumber"/>
    <result column="RECEIVE_ACCOUNT_NAME" property="receiveAccountName"/>
    <result column="CLASS_AND_GRADE" property="classAndGrade"/>
</resultMap>

<resultMap id="StatisticsResultMap"
           type="com.bozhong.satisfaction.domain.dto.response.EmployeeSatisfactionStatisticsDataRespDTO">
    <result column="RECEIVE_ACCOUNT_ID" property="receiveAccountId"/>
    <result column="RECEIVE_ACCOUNT_JOB_NUMBER" property="receiveAccountJobNumber"/>
    <result column="RECEIVE_ACCOUNT_NAME" property="receiveAccountName"/>
    <result column="CLASS_AND_GRADE" property="classAndGrade"/>
    <collection property="scoreList" ofType="com.bozhong.satisfaction.domain.dto.response.EmployeeSatisfactionStatisticsScoreRespDTO">
        <result column="RECEIVE_WARD_ID" property="receiveWardId"/>
        <result column="RECEIVE_WARD_NAME" property="receiveWardName"/>
        <result column="RATED_SATI_OPTION_SCORE" property="rateSatiOptionScore"/>
        <result column="CREATE_TIME" property="createTime"/>
    </collection>
</resultMap>

<select id="PgFactStaffSatiRecycleDetailDaoImpl_queryStaffSatisfactionStatisticsDataPage" parameterType="map"
        resultMap="StatisticsAccountResultMap">
    SELECT distinct detail.RECEIVE_ACCOUNT_ID,
           detail.RECEIVE_ACCOUNT_NAME,
           nd.JOB_NUMBER RECEIVE_ACCOUNT_JOB_NUMBER,
           nd.CLASS_AND_GRADE
    FROM SF_STAFF_SEND_RECYCLE_DETAIL detail
    LEFT JOIN NURSE_DOSSIER nd ON detail.RECEIVE_ACCOUNT_ID = nd.ID
    WHERE  detail.RECYCLE_STATUS = 1
      AND nd.VALID_FLAG = 1
      AND nd.TEACHING_TYPE = 'INTERNS'
      <if test="keyword != null and keyword != ''">
          AND (nd.JOB_NUMBER LIKE CONCAT(CONCAT('%',#{keyword}),'%')  OR nd.NAME LIKE CONCAT(CONCAT('%',#{keyword}),'%') )
      </if>
</select>

<select id="PgFactStaffSatiRecycleDetailDaoImpl_queryStaffSatisfactionStatisticsDataList" parameterType="map"
        resultMap="StatisticsResultMap">
    SELECT detail.RECEIVE_ACCOUNT_ID,
        detail.RECEIVE_ACCOUNT_NAME,
        detail.RECEIVE_WARD_ID,
        detail.RECEIVE_WARD_NAME,
        detail.RATED_SATI_OPTION_SCORE,
        detail.CREATE_TIME,
        nd.JOB_NUMBER RECEIVE_ACCOUNT_JOB_NUMBER,
        nd.CLASS_AND_GRADE
    FROM SF_STAFF_SEND_RECYCLE_DETAIL detail
    LEFT JOIN NURSE_DOSSIER nd ON detail.RECEIVE_ACCOUNT_ID = nd.ID
    WHERE detail.RECYCLE_STATUS = 1
    AND (detail.RECEIVE_ACCOUNT_ID IN
    <foreach item="item" index="index" collection="accountIdList" open="(" separator="," close=")">
        <if test="(index % 999) == 998">NULL) OR detail.RECEIVE_ACCOUNT_ID IN (</if>#{item,jdbcType=VARCHAR}
    </foreach>)
    ORDER BY detail.RECYCLE_TIME DESC
</select>

oracle递归查询（树型查询）

<select id="queryUnitTree" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from NURSE_UNIT
    where VALID_FLAG = 1
    start with id in
    <foreach collection="areaOrDeptIdList" index="index" item="item" open="(" separator="," close=")">
        #{areaOrDeptIdList[${index}]}
    </foreach>
    connect by prior id = parent_id
</select>

oracle varchar2类型改为clob类型

alter table QUERY_CONDITION_CONFIG rename column VALUE to VALUE_BAK;
alter table QUERY_CONDITION_CONFIG add(VALUE CLOB);
update QUERY_CONDITION_CONFIG set VALUE = trim(VALUE_BAK) WHERE 1=1;
COMMIT;
alter table QUERY_CONDITION_CONFIG drop column VALUE_BAK;

便捷sql语句

相关推荐

分类选择