使用的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

相关推荐

  1. 使用sql

    2024-04-04 01:32:02       5 阅读
  2. mybatisinclude和sql使用

    2024-04-04 01:32:02       4 阅读
  3. 使用sql创建数据库以及常用sql简介

    2024-04-04 01:32:02       18 阅读
  4. SQL中 WITH AS 使用方法

    2024-04-04 01:32:02       30 阅读
  5. 常用数据库SQL语句使用大全

    2024-04-04 01:32:02       20 阅读

最近更新

  1. flink mysql数据表同步SQL CDC

    2024-04-04 01:32:02       0 阅读
  2. 【QT进阶】Qt http编程之json解析的简单介绍

    2024-04-04 01:32:02       0 阅读
  3. 从0开始深入理解Spring(1)--SpringApplication构造

    2024-04-04 01:32:02       0 阅读
  4. kubeadm 升级 k8s集群 1.17到1.20

    2024-04-04 01:32:02       0 阅读
  5. SpringCloudAlibaba+RocketMQ实现对消息中间件的整合

    2024-04-04 01:32:02       0 阅读
  6. Docker中Kafka容器创建/更新Topic支持多分区

    2024-04-04 01:32:02       0 阅读

热门阅读

  1. 网络安全:筑牢防线,抵御恶意攻击

    2024-04-04 01:32:02       5 阅读
  2. springboot

    2024-04-04 01:32:02       3 阅读
  3. Oracle控制文件管理

    2024-04-04 01:32:02       2 阅读
  4. Oracle联机日志文件管理

    2024-04-04 01:32:02       2 阅读
  5. dlib中rectangle与opencv的rect的区别

    2024-04-04 01:32:02       5 阅读
  6. 0基础如何进入IT行业?

    2024-04-04 01:32:02       2 阅读
  7. os模块篇(十一)

    2024-04-04 01:32:02       3 阅读
  8. 借助ChatGPT写作:打造学术论文中的亮点与互动

    2024-04-04 01:32:02       5 阅读
  9. 零基础入门多媒体音频(6)-alsa(2)

    2024-04-04 01:32:02       4 阅读
  10. Spring Boot Actuator

    2024-04-04 01:32:02       4 阅读
  11. Collection中常用方法

    2024-04-04 01:32:02       5 阅读
  12. redis

    2024-04-04 01:32:02       3 阅读