sql删除重复数据（多个KEY值）

最新推荐文章于 2023-05-25 11:34:54 发布

原创最新推荐文章于 2023-05-25 11:34:54 发布 · 529 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql删除重复数据 #多个key值 #sql去重复

sql 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍如何使用SQL语句处理数据库中重复数据的问题，包括创建临时表进行数据筛选，执行删除操作去除重复项，并通过具体实例展示如何在特定场景下实现数据去重。

sql删除重复数据（多个KEY值）

// 创建一个临时表
select key1,key2 INTO #aaa from 表名 group by key1,key2 having count(*) > 1
//执行删除
DELETE FROM 表名 WHERE key1 IN(SELECT key1 FROM #aaa) 
 AND key2 IN(SELECT key2 FROM #aaa) 
 AND INDX NOT IN(SELECT  MIN(INDX) from 表名 group by key1,key2 having count(*) > 1)

--集报出口去重复
select STOCKOUT_NO,STOCKOUT_LINE_NO,TRAN_TYPE_CODE,RESERVER5 INTO #aaa from BMS_PROCESS_OUT group by STOCKOUT_NO,STOCKOUT_LINE_NO,TRAN_TYPE_CODE,RESERVER5 having count(*) > 1

SELECT *  FROM BMS_PROCESS_OUT WHERE STOCKOUT_NO IN(SELECT STOCKOUT_NO FROM #aaa) 
 AND STOCKOUT_LINE_NO IN(SELECT STOCKOUT_LINE_NO FROM #aaa)
 AND TRAN_TYPE_CODE IN(SELECT TRAN_TYPE_CODE FROM #aaa)
 AND RESERVER5 IN(SELECT RESERVER5 FROM #aaa)
 AND INDX NOT IN(SELECT  MIN(INDX) from BMS_PROCESS_OUT group by STOCKOUT_NO,STOCKOUT_LINE_NO,TRAN_TYPE_CODE,RESERVER5 having count(*) > 1)

查询重复数据
SELECT H_INDX,
                 GDSSEQNO,
               COUNT(1) AS num
        FROM dbo.BSWL_NEMS_LIST
        GROUP BY H_INDX,
                 GDSSEQNO
        HAVING COUNT(1) > 1

SELECT L.*
FROM dbo.BSWL_NEMS_LIST L
    INNER JOIN
    (
        SELECT H_INDX,
                 GDSSEQNO,
               COUNT(1) AS num
        FROM dbo.BSWL_NEMS_LIST
        GROUP BY H_INDX,
                 GDSSEQNO
        HAVING COUNT(1) > 1
    ) T
        ON L.H_INDX = T.H_INDX
           AND L.GDSSEQNO = T.GDSSEQNO
     ORDER BY L.H_INDX,L.GDSSEQNO