自动插入id，id必须唯一！

[ThankU]

数据库table1已经储存了大量数据，table1只有一列叫name。

现在我想增加一列叫id，然后插入自动标号（开始于BH10000，然后BH10001，...）

插入的顺序是按照name的ASC排序，id必须唯一！

比如table1:


name, id
AAA, BH10000，
BBB, BH10001，
CCC, BH10002，
...


请问简单的办法是怎么做？

[ThankU]

多谢！请问 WHERE (name < a.name) 是什么意思呀？ 是不是这个决定name不能重复的？

[JL99000]

这个做为判断条件，是为了取到10000这个中的后面一位是多少

[mugua604]

判断子表里的东西比主表里小的所有记录~~~

[bluedreams85]

mark

[elvis_gao]

CREATE TABLE TB (NAME VARCHAR(10))
INSERT INTO TB VALUES('AAA') 
INSERT INTO TB VALUES('BBB') 
INSERT INTO TB VALUES('CCC') 
alter table tb add id nvarchar(10)

update tb set tb.id=b.id
from tb,
(SELECT a.name , id = 'BH' + right('1000' + cast(row_number() over(order by name) - 1 as varchar),5) FROM tb a) b
 where tb.name=b.name

select * from tb

/*
NAME       id
---------- ----------
AAA        BH10000
BBB        BH10001
CCC        BH10002

*/

[hui_hui_2007]

如果name列没有重复值的话，可以这样，
1。在name列按正序建聚集索引，这样将按字符进行物理排序。
2。增加一个自增列。这是自动列的顺序一定是对的。但是这个自增列不是你要的，因为它是int型。
3。增加字符型的id列，将刚加的自增列转成字符型，并且前面加上"BH"
4。删除刚才建的自增列。

[fa_ge]

CREATE TABLE TB (NAME VARCHAR(10) , id varchar(7))
INSERT INTO TB VALUES('AAA',null) 
INSERT INTO TB VALUES('BBB',null) 
INSERT INTO TB VALUES('CCC',null) 
GO

--增加自动id int 从10000开始

alter table tb
add id int identity(10000,1)

--增加一列id1 ，这是我们真正需要的

alter table tb
add id1 varchar(10)

update tb
set id1='BH'+cast(id as varchar)

[中国风]

alter table T add Row int identity(1000,1)

alter table T add ID as ('BH'+rtrim(Row))--显示计算列

[中国风]

自编号可用函数和触发器实现：
http://blog.csdn.net/roy_88/archive/2006/12/01/1424370.aspx

[sunshinemos]

--如果name出现重复,必须使用临时表来做.
CREATE TABLE TB (NAME VARCHAR(10))
INSERT INTO TB VALUES('AAA')
INSERT INTO TB VALUES('BBB')
INSERT INTO TB VALUES('CCC')
INSERT INTO TB VALUES('CCC')
GO

--创建一临时表
select px = identity(int,1,1) , * into tmp from tb order by name

SELECT * , id = 'BH' + right('1000' + cast((SELECT COUNT(1) - 1 FROM tmp WHERE (name < a.name) or (name = a.name and px < a.px)) + 1 as varchar),5) FROM tmp a

DROP TABLE TB,TMP

/*
px NAME id
----------- ---------- ------------
1 AAA BH10000
2 BBB BH10001
3 CCC BH10002
4 CCC BH10003

（所影响的行数为 4 行）
*/同意这种

[dawugui]

CREATE TABLE TB (NAME VARCHAR(10))
INSERT INTO TB VALUES('AAA') 
INSERT INTO TB VALUES('BBB') 
INSERT INTO TB VALUES('CCC') 
GO

SELECT * , id = 'BH' + right('1000' + cast((SELECT COUNT(name) - 1 FROM tb WHERE name < a.name) + 1 as varchar),5) FROM tb a

DROP TABLE TB

/*
NAME       id           
---------- ------------ 
AAA        BH10000
BBB        BH10001
CCC        BH10002

（所影响的行数为 3 行）
*/

[dawugui]

--如果ID列存在,使用update
CREATE TABLE TB (NAME VARCHAR(10) , id varchar(7))
INSERT INTO TB VALUES('AAA',null) 
INSERT INTO TB VALUES('BBB',null) 
INSERT INTO TB VALUES('CCC',null) 
GO

update tb
set tb.id = t.px
from tb,(SELECT * , px = 'BH' + right('1000' + cast((SELECT COUNT(name) - 1 FROM tb WHERE name < a.name) + 1 as varchar),5) FROM tb a) t
where tb.name = t.name

select * from tb

DROP TABLE TB

/*
NAME       id           
---------- ------------ 
AAA        BH10000
BBB        BH10001
CCC        BH10002

（所影响的行数为 3 行）
*/

[dawugui]

--如果name出现重复,必须使用临时表来做.
CREATE TABLE TB (NAME VARCHAR(10))
INSERT INTO TB VALUES('AAA') 
INSERT INTO TB VALUES('BBB') 
INSERT INTO TB VALUES('CCC') 
INSERT INTO TB VALUES('CCC') 
GO

--创建一临时表
select px = identity(int,1,1) , * into tmp from tb order by name 

SELECT * , id = 'BH' + right('1000' + cast((SELECT COUNT(1) - 1 FROM tmp WHERE (name < a.name) or (name = a.name and px < a.px)) + 1 as varchar),5) FROM tmp a

DROP TABLE TB,TMP

/*
px          NAME       id           
----------- ---------- ------------ 
1           AAA        BH10000
2           BBB        BH10001
3           CCC        BH10002
4           CCC        BH10003

（所影响的行数为 4 行）
*/