create table a( id int identity(1,1) not null, id2 as id, c int )
create table ak( id int identity(1,1) not null, id2 as id, c int ) insert ak(c) select 1 union all select 2 union all select 3 select * from akid id2 c ----------- ----------- ----------- 1 1 1 2 2 2 3 3 3
只是在INSERT的时候和IDENTITY列相同 以后的可以被修改,不知道是否可行
create table a( id int identity(1,1) not null, id2 as id, c int )INSERT INTO a(c) SELECT 1UPDATE a SET id2 = 10SELECT * FROM a消息 271,级别 16,状态 1,第 1 行 无法修改列 'id2',因为该列是计算列。
那就在写INSERT INTO的时候多写一列....
有个表,有2 INT个字段,都需要IDENTITY(1, 1) 请问如何做到? 不使用触发器 本问题只是好奇 谢谢标识列一个表只能有一个,可以用函数试试CREATE VIEW v_GetDate AS SELECT dt=CONVERT(CHAR(8),GETDATE(),112) GO--得到新编号的函数 CREATE FUNCTION f_NextBH() RETURNS char(12) AS BEGIN DECLARE @dt CHAR(8) SELECT @dt=dt FROM v_GetDate RETURN( SELECT @dt+RIGHT(10001+ISNULL(RIGHT(MAX(BH),4),0),4) FROM tb WITH(XLOCK,PAGLOCK) WHERE BH like @dt+'%') END
create function ks3() returns int as begin return(select case when max(id) is null then 1 else max(id)+1 end from ask) end create table ask( id int identity(1,1) not null, id2 int default dbo.ks3(), c int ) insert ask(c) values(1) insert ask(c) values(2) insert ask(c) values(6) select * from ask drop table ask /* id id2 c ----------- ----------- ----------- 1 1 1 2 2 2 3 3 6(3 行受影响) */
IF OBJECT_ID('T') IS NOT NULL DROP TABLE TCREATE TABLE T( A INT IDENTITY, B INT, C VARCHAR(20) )INSERT INTO T (B,C) SELECT (SELECT ISNULL(MAX(A),0)+1 FROM T),'A'SELECT * FROM T
对SQL的同步不是很了解,想请问下create table ask( id int identity(1,1) not null, id2 int default dbo.ks3(), c int )这里用dbo.ks3() 会不会出现问题
create table ask( id int identity(1,1) not null, id2 int default dbo.ks3(), c int ) 还有个问题 假设id最大的列被删掉了, 所得到的max(id)会和identity列不同
IF OBJECT_ID('T') IS NOT NULL DROP TABLE TCREATE TABLE T( A INT IDENTITY, B INT, C VARCHAR(20) ) INSERT INTO T (B,C) SELECT (SELECT @@identity+1),'A'
IF OBJECT_ID('T') IS NOT NULL DROP TABLE TCREATE TABLE T( A INT IDENTITY, B INT, C VARCHAR(20) ) INSERT INTO T (B,C) SELECT (SELECT @@identity+1),'A'
ID那列删除?还是修改ID 如果是删除 那下次插入 还是会读取正确的
是有这个问题,identity这个值的得到的顺序是先执行的插入,然后再得到这个值;
[Quote=引用 27 楼 feixianxxx 的回复:] 引用 22 楼 antoniusguo 的回复: 对SQL的同步不是很了解,想请问下create function ks3() returns int as begin return(select case when max(id) is null then 1 else max(id)+1 end from ask) end GO create table ask( id int identity(1,1) not null, id2 int default dbo.ks3(), c int ) insert ask(c) values(1) insert ask(c) values(2) insert ask(c) values(6) select * from ask DELETE FROM ASK WHERE ID=3 INSERT INTO ASK(C) VALUES(7) SELECT * FROM ASK drop table ask /* 1 1 1 2 2 2 3 3 61 1 1 2 2 2 4 3 7 实践是检验真理的唯一标准,马爷爷说的 */[/code]
这个修要修改 INSERT INTO T (B,C) SELECT (SELECT isnull(@@identity,0)+1),'A'
IDENTITY列的seed好像是不会减少的,但是max是临时算出来的
1 4 A 2 2 B 3 3 C 我这里总是第一列有问题,为什么?后面倒是都对
YONG 这个吧 @@identity 是随时更新到最新的create function ks3() returns int as begin return(select case when max(id) is null then 1 else max(id)+1 end from ask) end GO create table ask( id int identity(1,1) not null, id2 int , c int ) INSERT INTO ask(id2,C) SELECT (SELECT isnull(@@identity,0)+1),1 INSERT INTO ask(id2,C) SELECT (SELECT isnull(@@identity,0)+1),2 INSERT INTO ask(id2,C) SELECT (SELECT isnull(@@identity,0)+1),7 select * from ask DELETE FROM ASK WHERE ID=3 INSERT INTO ask(id2,C) SELECT (SELECT isnull(@@identity,0)+1),0 SELECT * FROM ASK drop table ask /* id id2 c ----------- ----------- ----------- 1 5 1 2 2 2 4 4 0*/
INSERT INTO T (B,C) SELECT (SELECT isnull(@@identity,0)+1),'A'上次看到篇文章,说是@@IDENTITY不安全 但是用SCOPE_IDENTITY()不一定有值
6)我要获得tb上新插入记录的标识值,为什么我的@@identity全局函数取的值不对? SQL code--=********************************************** IF object_id('ta') IS NOT NULL DROP TABLE ta CREATE TABLE ta(id INT IDENTITY(1,1),b_id INT,logTime DATETIME NOT NULL DEFAULT GETDATE()) GO CREATE TRIGGER t ON tb FOR INSERT AS INSERT ta (b_id) SELECT id FROM INSERTED GOINSERT tb SELECT 'dd' SELECT @@identity --为什么这里得到的不是5,而是1? 说明: @@identity是个全局函数,返回当前会话所有作用域最后产生的标识值(执行SELECT @@identity之前的最后) 我们可以看到ta上有insert触发器 t,触发器里对ta进行了insert,所以这里得到的是ta中新增的标识值 建议使用 SCOPE_IDENTITY INSERT tb SELECT 'ww' SELECT SCOPE_IDENTITY() --这里得到6,因为它返回当前会话,当前作用域最后产生的标识值 --=**********************************************
create table ask( id int identity(1,1) not null, id2 int , c int ) INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),1 INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),2 INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),7 select * from ask DELETE FROM ASK WHERE ID=3 INSERT INTO ask(id2,C) SELECT (SELECT isnull(@@identity,0)+1),0 SELECT * FROM ASK drop table ask /* id id2 c ----------- ----------- ----------- 1 5 1 2 2 2 4 4 0*/
INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),1 INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),2 INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),7 select * from ask DELETE FROM ASK WHERE ID=3 INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),,0)+1),0 SELECT * FROM ASK drop table ask
--自已做标识列的例子,不自动重排编号,而是自动补号:--创建得到最大id的函数 create function f_getid() returns char(3) as begin declare @id intif 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 endlb_re: return(right('000'+cast(@id as varchar),3)) end go--创建表 create table tb(id char(3) 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--删除部分记录 delete from tb where name in('张三','张七','张八','张十')--显示删除后的结果 select * from tb--再次插入记录 insert into tb(name) values('李一') insert into tb(name) values('李二')--显示插入的结果 select * from tb order by id go--删除环境 drop table tb drop function f_getid/*--测试结果 id name ---- ---------- 001 李一 002 张四 003 张五 004 张六 005 李二 007 张九(所影响的行数为 6 行) --*/ 这个可以保持ID连续的
id int identity(1,1) not null,
id2 as id,
c int
)
id int identity(1,1) not null,
id2 as id,
c int
)
insert ak(c)
select 1 union all
select 2 union all
select 3
select * from akid id2 c
----------- ----------- -----------
1 1 1
2 2 2
3 3 3
以后的可以被修改,不知道是否可行
id int identity(1,1) not null,
id2 as id,
c int
)INSERT INTO a(c) SELECT 1UPDATE a SET id2 = 10SELECT * FROM a消息 271,级别 16,状态 1,第 1 行
无法修改列 'id2',因为该列是计算列。
请问如何做到? 不使用触发器 本问题只是好奇 谢谢标识列一个表只能有一个,可以用函数试试CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(8),GETDATE(),112)
GO--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
DECLARE @dt CHAR(8)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT @dt+RIGHT(10001+ISNULL(RIGHT(MAX(BH),4),0),4)
FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%')
END
create function ks3()
returns int
as
begin
return(select case when max(id) is null then 1 else max(id)+1 end from ask)
end
create table ask(
id int identity(1,1) not null,
id2 int default dbo.ks3(),
c int
)
insert ask(c) values(1)
insert ask(c) values(2)
insert ask(c) values(6)
select * from ask
drop table ask
/*
id id2 c
----------- ----------- -----------
1 1 1
2 2 2
3 3 6(3 行受影响)
*/
IF OBJECT_ID('T') IS NOT NULL DROP TABLE TCREATE TABLE T(
A INT IDENTITY,
B INT,
C VARCHAR(20)
)INSERT INTO T (B,C)
SELECT (SELECT ISNULL(MAX(A),0)+1 FROM T),'A'SELECT * FROM T
2、另外一列只能用公式的方式得到,但是计算列是不能修改的。楼主是突发意想还是?除了用触发器还能用别的办法实现?我也很好奇
id int identity(1,1) not null,
id2 int default dbo.ks3(),
c int
)这里用dbo.ks3()
会不会出现问题
id int identity(1,1) not null,
id2 int default dbo.ks3(),
c int
) 还有个问题
假设id最大的列被删掉了,
所得到的max(id)会和identity列不同
A INT IDENTITY,
B INT,
C VARCHAR(20)
)
INSERT INTO T (B,C)
SELECT (SELECT @@identity+1),'A'
A INT IDENTITY,
B INT,
C VARCHAR(20)
)
INSERT INTO T (B,C)
SELECT (SELECT @@identity+1),'A'
如果是删除 那下次插入 还是会读取正确的
引用 22 楼 antoniusguo 的回复:
对SQL的同步不是很了解,想请问下create function ks3()
returns int
as
begin
return(select case when max(id) is null then 1 else max(id)+1 end from ask)
end
GO
create table ask(
id int identity(1,1) not null,
id2 int default dbo.ks3(),
c int
)
insert ask(c) values(1)
insert ask(c) values(2)
insert ask(c) values(6)
select * from ask
DELETE FROM ASK WHERE ID=3
INSERT INTO ASK(C) VALUES(7)
SELECT * FROM ASK
drop table ask
/*
1 1 1
2 2 2
3 3 61 1 1
2 2 2
4 3 7
实践是检验真理的唯一标准,马爷爷说的
*/[/code]
INSERT INTO T (B,C) SELECT (SELECT isnull(@@identity,0)+1),'A'
2 2 B
3 3 C
我这里总是第一列有问题,为什么?后面倒是都对
@@identity 是随时更新到最新的create function ks3()
returns int
as
begin
return(select case when max(id) is null then 1 else max(id)+1 end from ask)
end
GO
create table ask(
id int identity(1,1) not null,
id2 int ,
c int
)
INSERT INTO ask(id2,C) SELECT (SELECT isnull(@@identity,0)+1),1
INSERT INTO ask(id2,C) SELECT (SELECT isnull(@@identity,0)+1),2
INSERT INTO ask(id2,C) SELECT (SELECT isnull(@@identity,0)+1),7
select * from ask
DELETE FROM ASK WHERE ID=3
INSERT INTO ask(id2,C) SELECT (SELECT isnull(@@identity,0)+1),0
SELECT * FROM ASK
drop table ask
/*
id id2 c
----------- ----------- -----------
1 5 1
2 2 2
4 4 0*/
但是用SCOPE_IDENTITY()不一定有值
SQL code--=**********************************************
IF object_id('ta') IS NOT NULL
DROP TABLE ta
CREATE TABLE ta(id INT IDENTITY(1,1),b_id INT,logTime DATETIME NOT NULL DEFAULT GETDATE())
GO
CREATE TRIGGER t ON tb
FOR INSERT
AS
INSERT ta (b_id) SELECT id FROM INSERTED
GOINSERT tb SELECT 'dd'
SELECT @@identity --为什么这里得到的不是5,而是1?
说明:
@@identity是个全局函数,返回当前会话所有作用域最后产生的标识值(执行SELECT @@identity之前的最后)
我们可以看到ta上有insert触发器 t,触发器里对ta进行了insert,所以这里得到的是ta中新增的标识值
建议使用 SCOPE_IDENTITY INSERT tb SELECT 'ww'
SELECT SCOPE_IDENTITY() --这里得到6,因为它返回当前会话,当前作用域最后产生的标识值
--=**********************************************
create table ask(
id int identity(1,1) not null,
id2 int ,
c int
)
INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),1
INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),2
INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),7
select * from ask
DELETE FROM ASK WHERE ID=3
INSERT INTO ask(id2,C) SELECT (SELECT isnull(@@identity,0)+1),0
SELECT * FROM ASK
drop table ask
/*
id id2 c
----------- ----------- -----------
1 5 1
2 2 2
4 4 0*/
INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),2
INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),0)+1),7
select * from ask
DELETE FROM ASK WHERE ID=3
INSERT INTO ask(id2,C) SELECT (SELECT isnull(SCOPE_IDENTITY(),,0)+1),0
SELECT * FROM ASK
drop table ask
好像在INSERT以后才有意义
第一次INSERT就用值是未知
而identity列的值会是2
create function f_getid()
returns char(3)
as
begin
declare @id intif 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
endlb_re:
return(right('000'+cast(@id as varchar),3))
end
go--创建表
create table tb(id char(3) 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--删除部分记录
delete from tb where name in('张三','张七','张八','张十')--显示删除后的结果
select * from tb--再次插入记录
insert into tb(name) values('李一')
insert into tb(name) values('李二')--显示插入的结果
select * from tb order by id
go--删除环境
drop table tb
drop function f_getid/*--测试结果
id name
---- ----------
001 李一
002 张四
003 张五
004 张六
005 李二
007 张九(所影响的行数为 6 行)
--*/
这个可以保持ID连续的
另外你那头像不错。:))