with t as ( select top 1 * from B where NO = 9988 order by newid() ) delete t
declare @id int select top 1 @id=id from B where NO = 9988 order by newid() delete b where id=@id
delete t from( select top 1 * from B where NO = 9988 order by newid() ) as t这两种都可以。
newid()这个排序结果不固定怎么删除?如果NO = 9988,为啥还要newid()
If not object_id('[b]') is null Drop table [b] Go Create table [b]([no] int,[善长球类列表] varchar(20),[name] nvarchar(2)) Insert [b] Select 9988,'1,11,111',N'小飞' union all Select 9988,'1,11',N'张三' union all Select 9988,'1',N'李四' Go --Select * from [b]-->SQL查询如下:delete t from( select top 1 * from B where NO = 9988 order by newid() ) as tselect * from b /* no 善长球类列表 name ----------- -------------------- ---- 9988 1,11,111 小飞 9988 1 李四(2 行受影响) */
派生表 't' 不可更新,因为其定义中包含 TOP 子句。
什么版本,你把9楼所有的代码COPY过去看能不能执行。
SQL 2000,还是一样的问题
2000试试4楼的,没ID再建一列: alter table tb add id int identity
大哥,我加个ID进去,就不用这么麻烦了,找到其中最大的ID号,把他删掉就可以了
If not object_id('[b]') is null Drop table [b] Go Create table [b]([no] int,[善长球类列表] varchar(20),[name] nvarchar(2)) Insert [b] Select 9988,'1,11,111',N'小飞' union all Select 9988,'1,11',N'张三' union all Select 9988,'1',N'李四' Go Select * from [b] delete b1 from b b1,(select top 1 * from B where NO = 9988 order by newid()) b2 where b1.[善长球类列表]=b2.[善长球类列表] and b1.[name]=b2.[name]select * from b /* no 善长球类列表 name ----------- -------------------- ---- 9988 1,11 张三 9988 1 李四(所影响的行数为 2 行)*/
(
select top 1 * from B where NO = 9988 order by newid()
)
delete t
select top 1 @id=id from B where NO = 9988 order by newid()
delete b where id=@id
from(
select top 1 * from B where NO = 9988 order by newid()
) as t这两种都可以。
Drop table [b]
Go
Create table [b]([no] int,[善长球类列表] varchar(20),[name] nvarchar(2))
Insert [b]
Select 9988,'1,11,111',N'小飞' union all
Select 9988,'1,11',N'张三' union all
Select 9988,'1',N'李四'
Go
--Select * from [b]-->SQL查询如下:delete t
from(
select top 1 * from B where NO = 9988 order by newid()
) as tselect * from b
/*
no 善长球类列表 name
----------- -------------------- ----
9988 1,11,111 小飞
9988 1 李四(2 行受影响)
*/
派生表 't' 不可更新,因为其定义中包含 TOP 子句。
SQL 2000,还是一样的问题
alter table tb add id int identity
大哥,我加个ID进去,就不用这么麻烦了,找到其中最大的ID号,把他删掉就可以了
Drop table [b]
Go
Create table [b]([no] int,[善长球类列表] varchar(20),[name] nvarchar(2))
Insert [b]
Select 9988,'1,11,111',N'小飞' union all
Select 9988,'1,11',N'张三' union all
Select 9988,'1',N'李四'
Go
Select * from [b]
delete b1 from b b1,(select top 1 * from B where NO = 9988 order by newid()) b2
where b1.[善长球类列表]=b2.[善长球类列表] and b1.[name]=b2.[name]select * from b
/*
no 善长球类列表 name
----------- -------------------- ----
9988 1,11 张三
9988 1 李四(所影响的行数为 2 行)*/