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

一尘不染

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

sql

我在批量插入脚本中犯了一个错误，所以现在我使用不同的colX来“复制”行。我需要删除这些重复的行，但是我无法弄清楚该怎么做。更确切地说，我有这个：

 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

我想保留每个（行，colX）的第一次出现：

 col1 | col2 | col3 | colX      
----+----------------------
  0   |  1   |  2   |  a
  3   |  4   |  5   |  x

谢谢您的回复：）

阅读 178

2021-05-23

共1个答案

一尘不染

尝试使用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 |

2021-05-23