删除SQL Server 2010中的“复制”行

删除SQL Server 2010中的“复制”行

尝试使用Sql Server CTE的最简单方法：http
://www.sqlfiddle.com/#!3/2d386 /2

数据：

CREATE TABLE tbl    ([col1] int, [col2] int, [col3] int, [colX] varchar(1));INSERT INTO tbl    ([col1], [col2], [col3], [colX])VALUES    (0, 1, 2, 'a'),    (0, 1, 2, 'b'),    (0, 1, 2, 'c'),    (0, 1, 2, 'a'),    (3, 4, 5, 'x'),    (3, 4, 5, 'y'),    (3, 4, 5, 'x'),    (3, 4, 5, 'z');

解决方案：

select * from tbl;with a as(  select row_number() over(partition by col1 order by col2, col3, colX) as rn   from tbl   )delete from a where rn > 1;select * from tbl;

输出：

| COL1 | COL2 | COL3 | COLX |-----------------------------|    0 |    1 |    2 |    a ||    0 |    1 |    2 |    b ||    0 |    1 |    2 |    c ||    0 |    1 |    2 |    a ||    3 |    4 |    5 |    x ||    3 |    4 |    5 |    y ||    3 |    4 |    5 |    x ||    3 |    4 |    5 |    z || COL1 | COL2 | COL3 | COLX |-----------------------------|    0 |    1 |    2 |    a ||    3 |    4 |    5 |    x |

---

也许这样：http :
//www.sqlfiddle.com/#!3/af826/1

数据：

CREATE TABLE tbl    ([col1] int, [col2] int, [col3] int, [colX] varchar(1));INSERT INTO tbl    ([col1], [col2], [col3], [colX])VALUES    (0, 1, 2, 'a'),    (0, 1, 2, 'b'),    (0, 1, 2, 'c'),    (0, 1, 2, 'a'),    (0, 1, 3, 'a'),    (3, 4, 5, 'x'),    (3, 4, 5, 'y'),    (3, 4, 5, 'x'),    (3, 4, 5, 'z');

解决方案：

select * from tbl;with a as(    select row_number() over(partition by col1, col2, col3 order by colX) as rn     from tbl   )delete from a where rn > 1;select * from tbl;

输出：

| COL1 | COL2 | COL3 | COLX |-----------------------------|    0 |    1 |    2 |    a ||    0 |    1 |    2 |    b ||    0 |    1 |    2 |    c ||    0 |    1 |    2 |    a ||    0 |    1 |    3 |    a ||    3 |    4 |    5 |    x ||    3 |    4 |    5 |    y ||    3 |    4 |    5 |    x ||    3 |    4 |    5 |    z || COL1 | COL2 | COL3 | COLX |-----------------------------|    0 |    1 |    2 |    a ||    0 |    1 |    3 |    a ||    3 |    4 |    5 |    x |