删除每第n行SQL [英] Deleting every nth row SQL

问题描述

说我有一个表（id int，Name varchar）为1000行。现在我想删除每第n个记录（每2，3或5）。

Say I have a table (id int, Name varchar) of 1000 rows. Now I wish to delete every nth record (every 2nd, 3rd or 5th) . What is the most efficient way to do this ?

推荐答案

对于SQL Server 2005 +

For SQL Server 2005+

WITH example AS (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rank
       FROM TABLE t)
DELETE example
   WHERE rank%2 = 0

对于每第3行，将WHERE子句更改为：

For every 3rd row, change the WHERE clause to:

WHERE rank%3 = 0

Anf每第五行：

WHERE rank%5 = 0

它返回除法的余数。如果余数为零，则被除的值是除数的倍数。

This uses modulus, which returns the remainder from division. If the remainder is zero, the value being divided is a multiple of the divisor.

这篇关于删除每第n行SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！