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

[BIRD72sky]

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

[billpu]

delete from tb_num where id in (5,6)

[billpu]

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

[hao1hao2hao3]

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))

[水族杰纶]

;with tt
as
(select * ,px=row_number()over(order by (select 1))
 from tb)
delete tt
where px between 5 and 6

[BIRD72sky]

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

[BIRD72sky]

[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]

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

[baiying15]

[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]

正解

[baiying15]

;with aa as
(
select *,row_number() over(order by getdate()) as rw
from tb
)
delete from aa
where rw between 5 and 6

[Rotel-刘志东]

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

delete from tb where id between 5 and 6 

[TOM_9562]

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

[feixianxxx]

[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]...

[abuying]

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

[jsjhj2008]

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

[sunny_yu]

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

[ka7iu]

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

[BIRD72sky]

[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]非常感谢给我解释，可惜分不多，没能给你了，下次一定送分，谢谢！

[BIRD72sky]

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

[xb12369]

咳骸 我来纠正一下：

正解的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
))