Question

附录:

ID ColA ColB
1   7    8
2   7    9
3   7    9
4   5    8
5   6    9
6   6    9
7   5    4

The PK is the ID coumn. Now, I want to delete all duplicates of ColA and ColB in consecutive rows.

In this example rows 2,3 and 5,6 contain duplicates. These shall be removed so that the higher ID is remained.

产出应为:

ID ColA ColB
1   7    8

3   7    9
4   5    8

6   6    9
7   5    4

如何用我的SQL来做到这一点?

Thanks, Juergen

Answer 1

SELECT 
    ID
FROM
    MyTable m1
WHERE
    0 < (SELECT 
            COUNT(*)
        FROM
            MyTable m2
        WHERE
            m2.ID = m1.ID - 1 AND 
            m2.ColA = m1.ColA AND 
            m2.ColB = m1.ColB)

之后,你可以使用

delete from MyTable where ID in ...

问题。这样,它肯定会以任何形式开展工作。

Answer 2

CREATE TEMPORARY TABLE duplicates (id int primary key)

INSERT INTO duplicates (id)
    SELECT t1.id
      FROM table t1
      join table t2 on t2.id = t1.id + 1
     WHERE t1.ColA = t2.ColA
       and t1.ColB = t2.ColB

-- SELECT * FROM duplicates --> are you happy with that? => delete
DELETE table
  FROM table
  join duplicates on table.id = duplicates.id

Answer 3

视你们的记录有多少,这也许最有效率:

SELECT (SELECT TOP 1 id FROM table WHERE colA = m.colA AND colB = m.colB ORDER BY id DESC) AS id, m.*
FROM (SELECT DISTINCT colA, colB
      FROM  table) m

由于我通常使用ms,可能会出现yn误,但这一想法应当类似。

Answer 4

我称之为表上测试。

第一,设立一个表格,列出哥伦布和哥伦布的所有相同组合:

create temporary table tmpTable (ColA int, ColB int);
insert into tmpTable select ColA,ColB from test group by ColA, ColB;

现在,在原表格中为哥伦瑞和哥伦布的每一种相同组合选择最高补贴。把它列入一个新表格(由于这些是我们不想删除的两行而称为ToKeep):

create temporary table idsToKeep (ID int);
insert into idsToKeep select (select max(ID) from test where test.ColA=tmpTable.ColA and test.ColB=tmpTable.ColB) from tmpTable;

最后,删除原表格中所有未装入女方的项目。表格:

delete from test where ID <> all (select ID from idsToKeep);

友情链接