使用的sql

根据CODE去重
SELECT
    * 
FROM
    ( SELECT count( camera_code ) AS count, camera_code FROM n_camera_basic GROUP BY camera_code ) t 
WHERE
    t.count >1
    
    
        DELETE FROM n_camera_basic 
WHERE
    camera_id NOT IN (SELECT 
        dt.minno
    FROM
        (SELECT 
            MIN(camera_id) AS minno
        FROM
            n_camera_basic
        GROUP BY camera_code) dt);

批量插入
<insert id="insertSubScribeBatchByViDeptSubscribe"
        parameterType="com.netintech.video.deptsubscribe.domain.ViDeptSubscribe">
    INSERT INTO vi_subscribe_camera (
    camera_code,
    camera_name,
    catalog_type,
    )
    <foreach item="item" collection="list" separator=" UNION ">
        select
        camera_code,
        camera_name,
        (SELECT UNIX_TIMESTAMP()) update_time_long
        FROM
        n_camera_basic
        WHERE del_flag = '0'
        AND orgs_id=#{item.deptId}
    </foreach>
</insert>

批量更新

<update id="updateViCameraForeachBatch" parameterType="com.netintech.video.newsubscribe.domain.ViSubscribeCamera">
    update vi_subscribe_camera
    <set>
        <trim prefix="subscribe_status = case" suffix="end,">
            <foreach collection="list" item="item">
                when id=#{item.id} then #{item.subscribeStatus}
            </foreach>
        </trim>
        <trim prefix="update_time = case" suffix="end,">
            <foreach collection="list" item="item">
                when id=#{item.id} then #{item.updateTime}
            </foreach>
        </trim>
        <trim prefix="update_time_long = case" suffix="end,">
            <foreach collection="list" item="item">
                when id=#{item.id} then (SELECT UNIX_TIMESTAMP())
            </foreach>
        </trim>
        <trim prefix="del_flag = case" suffix="end,">
            <foreach collection="list" item="item">
                when id=#{item.id} then #{item.delFlag}
            </foreach>
        </trim>
    </set>
    <where>
        id in
        <foreach collection="list" separator="," item="item" open="(" close=")">
            #{item.id}
        </foreach>
    </where>
</update>

分组,查询连续五个月没有出现V这个值就加1

SELECT
    z.YEARDATA yearData,
    sum(z.flag) ljtz
FROM
    (
    SELECT
        A.YEARDATA,
        CASE
            WHEN
            SUM(CASE WHEN DYFL = '劣V' THEN 1 ELSE 0 END) OVER (
        ORDER BY
            YEAR(TO_DATE(YEARDATA, 'YYYY-MM')),
            MONTH(TO_DATE(YEARDATA, 'YYYY-MM')) ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) > 0
        THEN 1
            ELSE 0
        END AS flag
    FROM
        YW_QVLHD a
    WHERE
        A.xqmc LIKE concat('%', '市长环保目标责任书考核', '%')
    ORDER BY
        A.YEARDATA
)Z
GROUP BY
    z.YEARDATA
ORDER BY
    z.YEARDATA