ID NewCode frequent
---------------------------------------------
1 HT-8008SR 1
2 HT-8008SR 1
3 HT-8008SF 1
4 HT-8008SF 1
?1.删除整个表中重复的ID号记录,只保留一条记录。并记录重复的次数?该如何实现!谢谢!
得到
ID NewCode frequent
---------------------------------------------
1 HT-8008SR 1
2 HT-8008SF 2
---------------------------------------------
1 HT-8008SR 1
2 HT-8008SR 1
3 HT-8008SF 1
4 HT-8008SF 1
?1.删除整个表中重复的ID号记录,只保留一条记录。并记录重复的次数?该如何实现!谢谢!
得到
ID NewCode frequent
---------------------------------------------
1 HT-8008SR 1
2 HT-8008SF 2
LZ表中ID是1,2,3,4没重复,NewCode有重复的,看楼主结果中也删去重复的了,
但出现次数frequent字段里面HT-8008SR和HT-8008SF都出现两次,为什么一个是1,一个是2?
---------- ------------------------------ ---------------
1 HT-8008SF 2
2 HT-8008SR 2
(SELECT MAX(rowID) FROM TABLE GROUP BY NEWCODE)
--删除重复记录。
CREATE TABLE DelDupRec(ID NUMBER, NewCode VARCHAR2(15), FREQUENT INT);INSERT INTO DelDupRec
SELECT 1,'HT-8008SR',1 FROM DUAL UNION
SELECT 2,'HT-8008SR',1 FROM DUAL UNION
SELECT 3,'HT-8008SF',1 FROM DUAL UNION
SELECT 4,'HT-8008SF',1 FROM DUAL;
/*CLEAN UP DIRTY DATA*/
UPDATE DelDupRec A
SET FREQUENT = (SELECT COUNT(ID) FROM DelDupRec
WHERE NewCode = A.NewCode GROUP BY NewCode);
DELETE FROM DelDupRec A
WHERE A.ROWID > (SELECT MIN(B.ROWID) FROM DelDupRec B
WHERE B.NewCode = A.NewCode);/*如果保留原ID号则不须做以下动作,而直接执行(A)步的SELECT*/
CREATE TABLE TMP_DEL AS
SELECT ROWNUM AS ID, ROWID AS KEY FROM DelDupRec;
/UPDATE DelDupRec A SET ID = (SELECT ID FROM TMP_DEL WHERE KEY = A.ROWID);
/DROP TABLE TMP_DEL;
/SELECT * FROM DelDupRec;
/****************************************************/(A)SELECT ROWNUM,NewCode, FREQUENT FROM DELDUPREC;