代码之家 › 专栏 › 技术社区 › Ash

狡猾的删除。我怎么办?

sql-delete sql

Ash · 技术社区 · 14 年前

我得到了一个有两列的表(两列都是int),有40万条记录(很多)。第一列是按升序排列的随机数。第二列有一条规则(现在不重要) 表中有1000条记录,这是例外情况。因此,没有“规则”,只有“-1”值的单元格。

如何删除~399000条记录,因此我希望在我的表中只留下-1的记录及其“邻居”(与-1的记录前后的记录)

更新 SQL Server 2K5 第一列值-是唯一的,但不是ID-S(不是++:d)

例子:

之前:

 20022518   13
 20022882   364
 20022885   -1
 20022887   5
 20022905   18
 20023200   295
 20023412   212
 20023696   284
 20024112   416
 20025015   903
 20025400   385
 20025401   -1
 20025683   283
 20025981   298
 20025989   8
 20026752   763
 20027779   1027
 20028344   565
 20028350   6
 20028896   546
 20028921   25
 20028924   -1
 20028998   77
 20029031   33
 20029051   20
 20029492   441
 20029530   38
 20029890   360

后:

 20022882   364
 20022885   -1
 20022887   5
 20025400   385
 20025401   -1
 20025683   283
 20028921   25
 20028924   -1
 20028998   77

4 回复 | 直到 10 年前

Albin Sunnanbo 14 年前

delete from table where not col1 in 
(
    (select col1 from table where col2 = -1)
union
    (select (select max(t2.col1) from table t2 where t2.col1 < t1.col1) from table t1 where t1.col2 = -1)
union
    (select (select min(t4.col1) from table t4 where t4.col1 > t3.col1) from table t3 where t3.col2 = -1)
)

t4.col1 < t3.col1 t4.col1 > t3.col1

SELECT * from adjacent

col1    col2
1   5
3   4
4   2
7   -1
11  8
12  2

SELECT * from adjacent
where
col1 in 
(
    (select col1 from adjacent where col2 = -1)
union
    (select (select max(t2.col1) from adjacent t2 where t2.col1 < t1.col1) from adjacent t1 where t1.col2 = -1)
union
    (select (select min(t4.col1) from adjacent t4 where t4.col1 > t3.col1) from adjacent t3 where t3.col2 = -1)
)

col1    col2
4   2
7   -1
11  8

not

SELECT * from adjacent
where
col1 not in 
(
    (select col1 from adjacent where col2 = -1)
union
    (select (select max(t2.col1) from adjacent t2 where t2.col1 < t1.col1) from adjacent t1 where t1.col2 = -1)
union
    (select (select min(t4.col1) from adjacent t4 where t4.col1 > t3.col1) from adjacent t3 where t3.col2 = -1)
)

col1    col2
1   5
3   4
12  2

delete from adjacent
where
col1 not in 
(
    (select col1 from adjacent where col2 = -1)
union
    (select (select max(t2.col1) from adjacent t2 where t2.col1 < t1.col1) from adjacent t1 where t1.col2 = -1)
union
    (select (select min(t4.col1) from adjacent t4 where t4.col1 > t3.col1) from adjacent t3 where t3.col2 = -1)
)

select * from adjacent

JNK 14 年前

SELECT *
INTO MyTable2
FROM MyTable
WHERE ColumnB = -1

DROP TABLE MyTable

exec sp_rename MyTable2 MyTable

DELETE

IDENTITY

SELECT *
INTO MyTable2
FROM MyTable mt
WHERE ColumnB = -1
OR WHERE EXISTS (
     SELECT * FROM MyTable mt2
     WHERE mt2.id = mt.id + 1
     OR mt2.id = mt.id -1)

DROP TABLE MyTable

exec sp_rename MyTable2 MyTable

smirkingman 14 年前

WITH Numbered(seq, id, ruleno) AS (
 SELECT
  ROW_NUMBER() OVER (ORDER BY id), id, ruleno
 FROM
  Tricky
),
Brothers(id, ruleno) AS (
 SELECT
  b.id, b.ruleno
 FROM
  Numbered a INNER JOIN Numbered b
  ON a.ruleno = -1 AND
  abs(a.seq - b.seq) = 1
),
Triplets(id, ruleno) AS (
 SELECT
  id, ruleno
 FROM
  Tricky
 WHERE
  ruleno = -1
 UNION ALL
 SELECT
  id, ruleno
 FROM
  Brothers
)
-- Display results
SELECT 
 id, ruleno
FROM
 Triplets
ORDER BY
 id

id ruleno
20022882 364
20022885 -1
20022887 5
20025400 385
20025401 -1
20025683 283
20028921 25
20028924 -1
20028998 77

DELETE FROM
  Tricky
WHERE
  id NOT IN (
    SELECT 
      id
    FROM
      triplets
  )

subhash 14 年前

http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/