34,838
社区成员




--如果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 行)
*/
--如果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 行)
*/
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 行)
*/
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)
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
*/