我想再数据库中弄一个简单的标示,让其在数据库中唯一。在插入是要让它自动在尾部增加,删除是要让它自动减少,并调整原来的顺序,让标示连续。
解决方案 »
- exec sp_helptext P_name 什么意思?
- 请教大侠,我在进行数据库完全备份恢复时出现提示"LSN(179:63:14)passed to log scan in database 'databasename' is invalid",不能恢复,
- 在SQL中如何复制一个已经存在的表?
- 带条件的join方式
- 高分:将SqlServer2000升级到2005,会不会有问题?
- 超难SQL性能调校,MVP终结者!走两步试试!
- SELECT top 4 *,count(*) FROM T_News 这条语句怎么报错?在线等!
- 更新表格出现的奇怪问题
- 存储过程查询
- 哪位高手有可完成以下"先进先出法"存取数据的例子?
- 找出最近的经纬度,并批量更新的问题
- 如何用 master..xp_cmdshell启动一个服务
IF NOT OBJECT_ID('[f_getid]') IS NULL
DROP function f_getid
GO
create function f_getid()
returns int
as
begin
declare @id int
if not exists(select 1 from tb where id='001')
set @id=1
else
begin
select @id=max(id) from tb
if @id is null
set @id=1
else
begin
declare @id1 int
select @id1=min(id) from tb a where id<>@id and not exists(select 1 from tb where id=a.id+1)
if @id1 is not null set @id=@id1
set @id=@id+1
end
end
lb_re:
return @id
end
go--创建表
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE tb
GO
create table tb(id int primary key default dbo.f_getid(),name varchar(10))
go
--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb
/*
id name
----------- ----------
1 张三
2 张四
3 张五
4 张六
5 张七
6 张八
7 张九
8 张十(8 行受影响)*/
--删除部分记录
delete from tb where name in('张三','张七','张八','张十')--显示删除后的结果
select * from tb
/*
id name
----------- ----------
2 张四
3 张五
4 张六
7 张九(4 行受影响)*/
--再次插入记录
insert into tb(name) values('李一')
insert into tb(name) values('李二')--显示插入的结果
select * from tb order by id
/*
id name
----------- ----------
1 李一
2 张四
3 张五
4 张六
5 李二
7 张九(6 行受影响)
*/--删除环境
drop table tb
drop function f_getid本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2009/07/23/4369943.aspx
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ttt]
GO/****** Object: Table [dbo].[ttt] Script Date: 2008-12-15 17:11:26 ******/
CREATE TABLE [dbo].[ttt] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[time] [datetime] NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[ttt] ADD
CONSTRAINT [PK_ttt] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOinsert into ttt(name,time) values('logan',getdate());
insert into ttt(name,time) values('peter',getdate());
insert into ttt(name,time) values('man',getdate());
insert into ttt(name,time) values('lida',getdate());
insert into ttt(name,time) values('fcuandy',getdate());select * from ttt
/*
1 logan 2008-12-15 17:36:37.780
2 peter 2008-12-15 17:36:37.780
3 man 2008-12-15 17:36:37.780
4 lida 2008-12-15 17:36:37.780
5 fcuandy 2008-12-15 17:36:37.793
*/
GO
CREATE TRIGGER tr ON ttt
INSTEAD OF INSERT
AS
SET IDENTITY_INSERT ttt ON
DECLARE @n INT
SELECT @n=MAX(id) FROM ttt
;WITH fc AS
(
SELECT n=1
UNION ALL
SELECT nn=n+1 FROM fc WHERE n<@n
),fc1 AS
(
SELECT n FROM fc a
LEFT JOIN ttt b
ON a.n = b.id
WHERE b.id IS NULL
)
INSERT ttt(id,name,time) SELECT n,name,time
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) idx,n FROM fc1) a
INNER JOIN
(
SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted
) b
ON a.idx=b.idx
DECLARE @r INT
SELECT @r=@@ROWCOUNT
SET IDENTITY_INSERT ttt OFF
INSERT ttt(name,time) SELECT name,time FROM
(
SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted
) x
WHERE idx>@r
GO
DELETE FROM ttt WHERE name = 'peter' OR name='lida'
GO
INSERT ttt SELECT 'xxx',getdate()
INSERT ttt SELECT 'yyy',GETDATE()
GO
SELECT * FROM ttt
/*
1 logan 2008-12-15 17:37:20.967
2 xxx 2008-12-15 17:37:21.013
3 man 2008-12-15 17:37:20.967
4 yyy 2008-12-15 17:37:21.030
5 fcuandy 2008-12-15 17:37:20.967
*/DELETE FROM ttt WHERE name ='xxx' OR name='yyy'INSERT ttt SELECT 'roy_88',GETDATE() UNION ALL SELECT 'limpire',GETDATE() UNION ALL SELECT '熊',GETDATE()SELECT * FROM ttt/*
1 logan 2008-12-15 17:38:29.450
2 roy_88 2008-12-15 17:38:29.530
3 man 2008-12-15 17:38:29.467
4 limpire 2008-12-15 17:38:29.530
5 fcuandy 2008-12-15 17:38:29.467
6 熊 2008-12-15 17:38:29.530
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fcuandy/archive/2008/12/15/3522876.aspx