sql语句中删除两条记录，id不连续

2010-09-10 12:19:25 303 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL Server ID 自增不连续、删除任意列数据后ID不连续的解决方案。在 SQL Server 中，删除一条数据不会导致自增列重置。自增列的值是根据表中已有的最大值自动递增生成的，而不是根据表中已删除的行数来计算的。因此，即使删除了一些行，自增列的下一个值仍然会基于表中已有的最大值来计算。如果想重置就需要删除表数据，重置自增ID。有多种方法，我统一使用T-SQL编写。阅读详情

要删除一张数据表中的第5、6条数据，用sql语句实现（id不是连续的），表名：Tb_num
各位帮个忙，谢谢了！

...全文

【MySQL】MySQL数据库主键自增长删除后ID不连续的问题及其解决方案 MySQL数据库主键自增长删除后ID不连续的问题及其解决方案阅读详情

BIRD72sky

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

xb12369 2012-10-25

打赏
举报

咳骸我来纠正一下：

正解的sql不是top 6 而是top2



select * from test where ID in(

select top 2 ID from test where ID not in (

select top 4 ID from test

))

BIRD72sky 2010-09-19

打赏
举报

[Quote=引用 8 楼 abuying 的回复:]
row_number()over
是SQL 2005新增的排序函数.
意是为结果集从1开始递增.
同理还有rank,ntile,意义不同,对于同名次的
[/Quote]非常感谢给我解释，多谢！下次一定送分！

BIRD72sky 2010-09-19

打赏
举报

[Quote=引用 7 楼 kevinwen0413 的回复:]
delete from tb where id in (select top 6 id from tb where id not in (select top 4 id from tb))
[/Quote]非常感谢给我解释，可惜分不多，没能给你了，下次一定送分，谢谢！

ka7iu 2010-09-13

打赏
举报

delete from tb where id in (select top 6 id from tb where id not in (select top 4 id from tb))

sunny_yu 2010-09-13

打赏
举报

delete from table where rowid=5,lz的意思根据行号删除吧

jsjhj2008 2010-09-13

打赏
举报

delete from tb where id in (select top 6 id from tb where id not in (select top 4 id from tb))

abuying 2010-09-13

打赏
举报

row_number()over
是SQL 2005新增的排序函数.
意是为结果集从1开始递增.
同理还有rank,ntile,意义不同,对于同名次的

feixianxxx 2010-09-13

打赏
举报

[Quote=引用 4 楼 wufeng4552 的回复:]

SQL code
;with tt
as
(select * ,px=row_number()over(order by (select 1))
from tb)
delete tt
where px between 5 and 6
[/Quote]...

TOM_9562 2010-09-13

打赏
举报

delete from tb where id in (select top 6 id from tb where id not in (select top 4 id from tb))

Rotel-刘志东 2010-09-13

打赏
举报

写法还是比较多的
能用between.... and就不用in

delete from tb where id between 5 and 6

baiying15 2010-09-13

打赏
举报



;with aa as

(

select *,row_number() over(order by getdate()) as rw

from tb

)

delete from aa

where rw between 5 and 6

baiying15 2010-09-13

打赏
举报

[Quote=引用 4 楼 wufeng4552 的回复:]
SQL code
;with tt
as
(select * ,px=row_number()over(order by (select 1))
from tb)
delete tt
where px between 5 and 6
[/Quote]

正解

kevn 2010-09-10

打赏
举报

delete from tb where id in (select top 6 id from tb where id not in (select top 4 id from tb))

BIRD72sky 2010-09-10

打赏
举报

[Quote=引用 4 楼 wufeng4552 的回复:]
SQL code
;with tt
as
(select * ,px=row_number()over(order by (select 1))
from tb)
delete tt
where px between 5 and 6
[/Quote]这个很有用，非常感谢！

BIRD72sky 2010-09-10

打赏
举报

[Quote=引用 1 楼 billpu 的回复:]
delete from tb_num where id in (5,6)
[/Quote]id不是连续的，就是说，删除的时候不能根据id删除，最近脑子很秀，还望详细些，谢谢

水族杰纶 2010-09-10

打赏
举报

;with tt

as

(select * ,px=row_number()over(order by (select 1))

 from tb)

delete tt

where px between 5 and 6

hao1hao2hao3 2010-09-10

打赏
举报



delete from tb where id in (select top 6 id from tb where id not in (select top 5 s from tb order by id asc))

billpu 2010-09-10

打赏
举报

如果id 没作用,你只要第几第几条的话
那就用top自己拼吧,精华贴里有

billpu 2010-09-10

打赏
举报

delete from tb_num where id in (5,6)

sql语句中删除两条记录，id不连续

相关推荐