34,593
社区成员
发帖
与我相关
我的任务
分享sql 更新一字段内容让其有规律
表 test:
aid pp rr oo
1 0158 iu145 ii
2 55 d
3 99 oi uty01
4 as df
5 ee e
6 iuy u uyt
7 o09 o
8 pol p p
9 qwe rty 1425
10 qwr q 14
11 tyn m
12 zxc yu 78
问题:以上为表资料,因字段pp无规律,现想将其变为ak001,ak002---ak012的规律命名(资料有可能不止12笔,命名规则自动向后排序),请大家帮帮我,谢谢!
---
------为了方便作业,我直接将创建表的语句贴出,大家直接执行即可.谢谢!
create table test (aid int identity(1,1) primary key,pp varchar(10),rr varchar(10),co varchar(10))
--创建表
--插入表资料
insert test
select '55','d',''
union
select 'ee','e',''
union
select 'o09','o',''
union
select 'pol','p','p'
union
select 'iuy','u','uyt'
union
select 'qwr','q','14'
union
select 'tyn','m',''
union
select '99','oi','uty01'
union
select 'qwe','rty','1425'
union
select '0158','iu145','ii'
union
select 'zxc','yu','78'
union
select 'as','df',''
...全文
请发表友善的回复…
发表回复
renadg 2009-11-03
- 打赏
- 举报
大家说说,我创建表,插入资料有啥问题吗?
huangqingqingxiao1 2009-11-03
- 打赏
- 举报
顶啊
zhousq00 2009-11-03
- 打赏
- 举报
我觉得1楼的语句就可以了
lb6201883 2009-11-03
- 打赏
- 举报
update test set pp='ak'+
case len(convert(varchar(8),aid)) when 1 then '00'+convert(varchar(8),aid) when 2 then '0'+convert(varchar(8),aid) else convert(varchar(8),aid) end
case len(convert(varchar(8),aid)) when 1 then '00'+convert(varchar(8),aid) when 2 then '0'+convert(varchar(8),aid) else convert(varchar(8),aid) end
fhjzgsy 2009-11-03
- 打赏
- 举报
顶
renadg 2009-11-03
- 打赏
- 举报
[Quote=引用 9 楼 js_szy 的回复:]
引用 4 楼 liangck 的回复:
SQL codecreatetable test (aidintidentity(1,1)primarykey,ppvarchar(10),rrvarchar(10),covarchar(10))--创建表--插入表资料insert testselect'55','d',''unionselect'ee','e',''unionselect'o09','o',''unionselect'pol',¡
用union 好像顺序乱了
[/Quote]
那你觉得应该如何写.....
引用 4 楼 liangck 的回复:
SQL codecreatetable test (aidintidentity(1,1)primarykey,ppvarchar(10),rrvarchar(10),covarchar(10))--创建表--插入表资料insert testselect'55','d',''unionselect'ee','e',''unionselect'o09','o',''unionselect'pol',¡
用union 好像顺序乱了
[/Quote]
那你觉得应该如何写.....
fwacky 2009-11-03
- 打赏
- 举报
create table test (aid int identity(1,1) primary key,pp varchar(10),rr varchar(10),co varchar(10))
--创建表
--插入表资料
insert test
select '55','d',''
union
select 'ee','e',''
union
select 'o09','o',''
union
select 'pol','p','p'
union
select 'iuy','u','uyt'
union
select 'qwr','q','14'
union
select 'tyn','m',''
union
select '99','oi','uty01'
union
select 'qwe','rty','1425'
union
select '0158','iu145','ii'
union
select 'zxc','yu','78'
union
select 'as','df',''
select aid,pp = 'ak'+right((1000+aid),3), rr,co from test
==================
aid pp rr co
----------- -------- ---------- ----------
1 ak001 iu145 ii
2 ak002 d
3 ak003 oi uty01
4 ak004 df
5 ak005 e
6 ak006 u uyt
7 ak007 o
8 ak008 p p
9 ak009 rty 1425
10 ak010 q 14
11 ak011 m
12 ak012 yu 78
(12 行受影响)
华夏小卒 2009-11-03
- 打赏
- 举报
[Quote=引用 4 楼 liangck 的回复:]
SQL codecreatetable test (aidintidentity(1,1)primarykey,ppvarchar(10),rrvarchar(10),covarchar(10))--创建表--插入表资料insert testselect'55','d',''unionselect'ee','e',''unionselect'o09','o',''unionselect'pol',¡
[/Quote]
用union 好像顺序乱了
SQL codecreatetable test (aidintidentity(1,1)primarykey,ppvarchar(10),rrvarchar(10),covarchar(10))--创建表--插入表资料insert testselect'55','d',''unionselect'ee','e',''unionselect'o09','o',''unionselect'pol',¡
[/Quote]
用union 好像顺序乱了
华夏小卒 2009-11-03
- 打赏
- 举报
declare @test table(aid int identity(1,1) primary key,pp varchar(10),rr varchar(10),co varchar(10))
--创建表
insert @test
select '55','d',''
union all
select 'ee','e',''
union all
select 'o09','o',''
union all
select 'pol','p','p'
union all
select 'iuy','u','uyt'
union all
select 'qwr','q','14'
union all
select 'tyn','m',''
union
select '99','oi','uty01'
union all
select 'qwe','rty','1425'
union all
select '0158','iu145','ii'
union all
select 'zxc','yu','78'
union all
select 'as','df',''
union all
select 'as','df','' ----加一条测试
declare @n int,@str varchar(10)
set @n=0
update @test
set @n= case when @n!=12 then @n+1 else 1 end ,pp='ak'+right('000'+ltrim(@n),3)
select * from @test
aid pp rr co
----------- ---------- ---------- ----------
1 ak001 d
2 ak002 oi uty01
3 ak003 e
4 ak004 u uyt
5 ak005 o
6 ak006 p p
7 ak007 q 14
8 ak008 m
9 ak009 rty 1425
10 ak010 iu145 ii
11 ak011 yu 78
12 ak012 df
13 ak001 df
(13 行受影响)
fwacky 2009-11-03
- 打赏
- 举报
update tb set pp='ak'+right(1000+aid,3)
tongnaifu 2009-11-03
- 打赏
- 举报
update test set
pp='Ak'+replicate('0',3-len(aid))+aid
--小F-- 2009-11-03
- 打赏
- 举报
如果aid是自增的话，可以直接
update tb set pp='ak'+right(1000+aid,3)
liangCK 2009-11-03
- 打赏
- 举报
create table test (aid int identity(1,1) primary key,pp varchar(10),rr varchar(10),co varchar(10))
--创建表
--插入表资料
insert test
select '55','d',''
union
select 'ee','e',''
union
select 'o09','o',''
union
select 'pol','p','p'
union
select 'iuy','u','uyt'
union
select 'qwr','q','14'
union
select 'tyn','m',''
union
select '99','oi','uty01'
union
select 'qwe','rty','1425'
union
select '0158','iu145','ii'
union
select 'zxc','yu','78'
union
select 'as','df',''
declare @i int;
update test set
@i = isnull(@i,0) + 1,
pp = 'ak'+right(1000+@i,3);
select * from test;
drop table test
/*
aid pp rr co
----------- ---------- ---------- ----------
1 ak001 iu145 ii
2 ak002 d
3 ak003 oi uty01
4 ak004 df
5 ak005 e
6 ak006 u uyt
7 ak007 o
8 ak008 p p
9 ak009 rty 1425
10 ak010 q 14
11 ak011 m
12 ak012 yu 78
(12 行受影响)
*/
chuifengde 2009-11-03
- 打赏
- 举报
update [Table] set pp='ak'+right(1000+aid,3)--小F-- 2009-11-03
- 打赏
- 举报
---参考：
在学习中遇到这个问题
数据库里有编号字段
BH00001
BH00002
BH00003
BH00004
如何实现自动增长
--下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO
--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
--插入资料
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN
--显示结果
SELECT * FROM tb
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/liangCK 2009-11-03
- 打赏
- 举报
declare @i int;
update test set
@i = isnull(@i,0) + 1,
pp = 'ak'+right(1000+@i,3);
select * from test;
bing110 2009-11-03
- 打赏
- 举报
呵呵，高手很多呀。凑个热闹。
