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

最新推荐文章于 2024-08-23 15:25:14 发布

王某宁没有肌肉

最新推荐文章于 2024-08-23 15:25:14 发布

阅读量464

点赞数

分类专栏： sql 文章标签： sql删除重复数据多个key值 sql去重复

本文链接：https://blog.csdn.net/python_no1/article/details/101108611

版权

sql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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