征求SQL语句

[nuaawyd]

数据库表AAA（a1,a2,a3）
a1 a2 a3
51000 1 2
51030 1 2
52000 1 2
52933 1 1
67020 1 2

现在要做如下处理，判断a字段的前两位，如果是51，则将51替换成101，如果是52则将替换成102，如果是67则将替换成103
生成BBB表（b1,b2,b3），结果如下
b1 b2 b3
101000 1 2
101030 1 2
102000 1 2
102933 1 1
103020 1 2


征求SQL语句

[wzy_love_sly]

create table a(a1 varchar(20),a2 int,a3 int)
insert into a select '51000',1,2
insert into a select '51030',1,2
insert into a select '52000',1,2
insert into a select '52933',1,1
insert into a select '67020',1,2

select 
case 
when left(a1,2)='51' then '101'+ right(a1,len(a1)-1)
when left(a1,2)='52' then '102' + right(a1,len(a1)-1)
when left(a1,2)='67' then '103' + right(a1,len(a1)-1)
else a1
end as a1 ,a2,a3
from a

a1 a2 a3
1011000 1 2
1011030 1 2
1022000 1 2
1022933 1 1
1037020 1 2

[wzy_love_sly]

ok结帖吧，要不又没分了

[$扫地僧$]

select case when left(a1,2)='51' then '101'+ right(a1,len(a1)-2) 
                  when left(a1,2)='52' then '102'+ right(a1,len(a1)-2) 
                  when left(a1,2)='67' then '103'+ right(a1,len(a1)-2) end b1,
       a2 as B2,a3 as B3 into BBB
from AAA

[pt1314917]

select 
a1=case 
when left(a1,2)='51' then '101'+ right(a1,len(a1)-2)
when left(a1,2)='52' then '102' + right(a1,len(a1)-2)
when left(a1,2)='67' then '103' + right(a1,len(a1)-2) end,a2,a3
from aaa

[wzy_love_sly]

create table a(a1 varchar(20),a2 int,a3 int)
insert into a select '51000',1,2
insert into a select '51030',1,2
insert into a select '52000',1,2
insert into a select '52933',1,1
insert into a select '67020',1,2

select 
case 
when left(a1,2)='51' then '101'+ right(a1,len(a1)-1)
when left(a1,2)='52' then '102' + right(a1,len(a1)-1)
when left(a1,2)='67' then '103' + right(a1,len(a1)-1)
else a1
end as a1 ,a2,a3
into bb
from a

select * from bb

a1 a2 a3
1011000 1 2
1011030 1 2
1022000 1 2
1022933 1 1
1037020 1 2

[dawugui]

create table a(a1 varchar(10) , a2 int , a3 int)
insert into a values('51000',1,2)
insert into a values('51030',1,2)
insert into a values('52000',1,2)
insert into a values('52933',1,1)
insert into a values('67020',1,2)
go

update a
set a1 = case left(a1,2) when '51' then '101' when '52' then '102' when '67' then '103' end + substring(a1,3,len(a1))

select * from A

drop table A

/*
a1                   a2          a3          
-------------------- ----------- ----------- 
101000               1           2
101030               1           2
102000               1           2
102933               1           1
103020               1           2
（所影响的行数为 5 行）
*/

[-狙击手-]

create table a(a1 varchar(20),a2 int,a3 int)
insert into a select '51000',1,2
insert into a select '51030',1,2
insert into a select '52000',1,2
insert into a select '52933',1,1
insert into a select '67020',1,2

select 
case left(a1,2)
when '51' then '101'+ right(a1,len(a1)-1)
when '52' then '102' + right(a1,len(a1)-1)
when '67' then '103' + right(a1,len(a1)-1)
else a1
end as a1 ,a2,a3
from a

drop table a

/*
a1                      a2          a3          
----------------------- ----------- ----------- 
1011000                 1           2
1011030                 1           2
1022000                 1           2
1022933                 1           1
1037020                 1           2

（所影响的行数为 5 行）

*/

[nuaawyd]

只有dawugui老兄的是对的，其他人的我也没研究是什么地方不对，其他大侠再检查一下问题

[dawugui]

嘿嘿,那就赶快结帖,顺便给他们三两分.

[lhsxsh]

select case when left(a1,2)='51' then '101'+ right(a1,len(a1)-2)
when left(a1,2)='52' then '102'+ right(a1,len(a1)-2)
when left(a1,2)='67' then '103'+ right(a1,len(a1)-2) end b1,
a2 as B2,a3 as B3 into BBB
from AAA