该SQL语句用于更新tsgz_network_info_app表，将delete_flag设置为1，目标是删除特定条件下（network_access_type=2且delete_flag=0）的重复记录。通过子查询和ROW_NUMBER()函数，对tsgz_dept_app_id和adsl相同的记录进行排序，当row_num大于1时，标记为待删除，从而消除重复行。
UPDATE tsgz_network_info_app
SET delete_flag = 1
WHERE id IN (
SELECT id
FROM (
SELECT id,tsgz_dept_app_id,adsl,ROW_NUMBER() OVER (PARTITION BY tsgz_dept_app_id, adsl ORDER BY id) AS row_num
FROM tsgz_network_info_app
WHERE network_access_type = 2 and delete_flag=0
) tmp
WHERE tmp.row_num > 1
);
根据tsgz_dept_app_id ,adsl 筛出重复的 ROW_NUMBER() OVER (PARTITION BY tsgz_dept_app_id, adsl ORDER BY id) AS row_num 使用这个函数进行筛选
2312
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
