34,590
社区成员
发帖
与我相关
我的任务
分享
if object_id('[A]') is not null drop table [A]
create table [A]([字段1] varchar(1),[字段2] int)
insert [A]
select 'a',null union all
select 'b',null union all
select 'c',null union all
select 'd',null
declare @n int
set @n=0
update A set [字段2]=@n, @n=@n+1
select * from [A]
if object_id('[A]') is not null drop table [A]
create table [A]([字段1] varchar(1),[字段2] int)
insert [A]
select 'a',null union all
select 'b',null union all
select 'c',null union all
select 'd',null union all
select 'e' ,null
update t
set 字段2 = rn
from (
select a.*,rn = row_number() over (order by 字段1) from a
)t
select * from a
delete a where 字段1 = 'c'
create trigger tr on a
instead of insert
as
begin
declare @rn int,@a varchar(2)
select @a = 字段1 from inserted
select @rn= max(字段2) from
(
select *,rn = row_number() over (order by 字段1) from a
)t
where 字段2 = rn
insert a values(@a,@rn+1)
end
insert a values('f',null)
select * from a
字段1 字段2
---- -----------
a 1
b 2
f 3
d 4
e 5
(5 row(s) affected)
插入时
if object_id('[A]') is not null drop table [A]
create table [A]([字段1] varchar(1),[字段2] int)
insert [A]
select 'a',null union all
select 'b',null union all
select 'c',null union all
select 'd',null
update t
set 字段2 = rn
from (
select a.*,rn = row_number() over (order by 字段1) from a
)t
select * from a
字段1 字段2
---- -----------
a 1
b 2
c 3
d 4
(4 row(s) affected)
DECLARE @tb TABLE (col1 varchar(10),col2 VARCHAR(32))
INSERT @tb
select 'a','2' UNION ALL
select 'b','2' UNION ALL
select 'c','2' UNION ALL
select 'd','2'
select [@tb].[col1],[@tb].[col2], ROW_NUMBER() OVER (ORDER BY [@tb].[col2]) NUMBER from @tb
col1 col2 NUMBER
---------- -------------------------------- --------------------
a 2 1
b 2 2
c 2 3
d 2 4
第二条: (insert ...)如果我删除了某条记录,再次插入记录时,字段2中能自动补全我刚刚删除的某条记录的字段2的值吗?
比如我删除了第二条记录
字段1 字段2
a 1
c 3
d 4
... ...
当我再次插入一条记录时,自动变成
字段1 字段2
a 1
c 3
d 4
... ...
xx 2 <---------我添加的记录(事先我并不知道是字段2的值为2的记录被删除了)
create table tb(字段1 varchar(10) , 字段2 int)
insert into tb values('a' , 1 )
insert into tb values('c' , 3 )
insert into tb values('d' , 4 )
go
insert into tb select 'xx',
(select top 1 px from
(
select * , px = (select count(1) from tb where 字段2 < t.字段2) + 1 from tb t
) t where 字段2 <> px)
select * from tb order by 字段2
drop table tb
/*
字段1 字段2
---------- -----------
a 1
xx 2
c 3
d 4
(所影响的行数为 4 行)
*/
--> By dobear_0922(小熊) 2009-02-10 16:19:36
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
create table [A]([字段1] varchar(1),[字段2] int)
insert [A]
select 'a',null union all
select 'b',null union all
select 'c',null union all
select 'd',null
declare @n int
set @n=0
update A set [字段2]=@n, @n=@n+1
select * from [A]
/*
字段1 字段2
---- -----------
a 1
b 2
c 3
d 4
(4 行受影响)
*/
go
create trigger tr1 on A
instead of insert
as
begin
declare @n int
select @n=min([字段2]) from A b
where not exists(select 1 from A where [字段2]=b.[字段2]+1)
insert A select [字段1], [字段2]=@n+1 from inserted
end
go
insert A select 'e',6
delete from A where [字段2]=2
insert A select 'f',null
select * from A
/*
字段1 字段2
---- -----------
a 1
f 2
c 3
d 4
e 5
(5 行受影响)
*/
drop table A
select 字段1 , 字段2 = (select count(1) from tb where 字段2 < t.字段2) + 1 from tb t
--1
if object_id('tb')is not null drop table tb
go
create table tb(col1 varchar(10), col2 varchar(10))
insert tb select 'a',null
insert tb select 'b' ,null
insert tb select 'c',null
insert tb select 'd' ,null
declare @i int
set @i=0
update tb set col2=@i ,@i=@i+1
select * from tb
/*col1 col2
---------- ----------
a 1
b 2
c 3
d 4
*/
--2
if object_id('tri_update')is not null drop trigger tri_update
go
create trigger tri_update on tb
for delete
as
declare @i int
set @i=0
update tb set col2=@i ,@i=@i+1
go
delete tb where col1='c'
select * from tb
/*col1 col2
---------- ----------
a 1
b 2
d 3*/
--1
if object_id('tb')is not null drop table tb
go
create table tb(col1 varchar(10), col2 varchar(10))
insert tb select 'a',null
insert tb select 'b' ,null
insert tb select 'c',null
insert tb select 'd' ,null
declare @i int
set @i=0
update tb set col2=@i ,@i=@i+1
select * from tb
/*col1 col2
---------- ----------
a 1
b 2
c 3
d 4
*/
--> By dobear_0922(小熊) 2009-02-10 16:19:36
--> 测试数据:[A1]
if object_id('[A]') is not null drop table [A]
create table [A]([字段1] varchar(1),[字段2] int)
insert [A]
select 'a',null union all
select 'b',null union all
select 'c',null union all
select 'd',null
declare @n int
set @n=0
update A set [字段2]=@n, @n=@n+1
select * from [A]
/*
字段1 字段2
---- -----------
a 1
b 2
c 3
d 4
(4 行受影响)
*/
drop table A