--RT,我知道可以delete top (10) * from tb,但是这个top是根据什么排序的呢?
delete top (10) * from tb order by col desc
--加上order by果断报错了,这个2005新加的delete top用法到底神马情况呢
解决方案 »
- 请教: 使用Identity作为主键好吗?
- 一个SELECT语句引发的迭代(SELECT @local_variable = expression 的妙用)
- SQL中的字符串替换
- sql server 2000里面哪个数据类型是日期时间型阿,要怎么往里面写啊?
- Help~~表合并问题
- 求教,SQL2008R2数据库附加时候提示偏移量错误
- 初学者的问题,请各位务必要帮个忙!!
- 如何在oracle里改表的列名
- MMC 管理单元初始化问题
- 启用sqlserver 的行和页 压缩的影响
- MS sql 2008 server多用户的问题
- |zyciis| 求一句SQL更新语句,批量更新记录,且按指定序号更新
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-12 15:08:29
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6
--------------开始查询--------------------------
delete t from (select top 5 * from tb order by col desc )t
select * from tb
----------------结果----------------------------
/* col
-----------
1(1 行受影响)*/
只影响到正好线访问到的行数
delete top (10) * from tb order by col desc
一般要通过
cte
用row_number产生序号 再根据序号删除 达到order by 的目的
指定将要删除的任意行数或任意行的百分比。expression 可以为行数或行的百分比。与 INSERT、UPDATE 或 DELETE 一起使用的 TOP 表达式中被引用行将不按任何顺序排列。在 INSERT、UPDATE 和 DELETE 语句中,需要使用括号分隔 TOP 中的 expression。
declare @T table (col int)
insert into @T
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10delete top (5) from @T
select * from @t
/*
col
-----------
6
7
8
9
10
*/
要么这个top的顺序不是随机的么
insert into tb select 1 union all select 2 union all select 3 union all select 4 union all select 5
go
delete top (3) from tb
go
select * from tb
go
drop table tb
/*
id
-----------
4
5(2 行受影响)*/
delete from table where col in (select col from table top(10) order by col desc )
一般
;with cte_del
as
(select top (n) from tb order by col)
delete cte_del
delete from tb where col in (select top(10) col from tb order by col desc )
可以,但应改一下:
delete table where col in (select top(10) col from table order by col desc )
其实我是想问delete top ...后面怎么能加上order by
不过貌似微软就不让这样,悲催