请教表A如何由表B对应相同的值做转换:
表A:A1 (1) (2) (3) (1,2),(2,3) (1,2,3)
B1 (3) (6) (8) (3,6),(6,8) (3,6,8)
C1 (3) (6) (3,6)
…
表B:
(1) a
(2) b
(3) c
(6) d
(8) e
(1,2) f
(2,3) g
(3,6) h
(6,8) i
(1,2,3) j
(3,6,8) k
…
结果:A1 a b c f,g j
B1 c d e h,i k
C1 c d h
解决方案 »
- bulk insert 不能传变量吗?
- 更新数据库的一个表中的属性值
- bom问题
- 分数区间统计
- 关于列(900列)过多的问题?
- 这个触发器该怎么写……
- 求助:如何在sql server 7.0或 2000中实现205,205001,205002;206,206001,206002
- 我今天无意中居然在2000professor上装上了sql developer edition,请问何故
- 未安装SQL Server2000的机子能否运行用VB6.0+SQL Server2000编写的程序
- 大佬们看下这是咋回事
- 为什么在SQL的查询分析器里面调用存储过程的时候提示:“对于局部变量,text、ntext 和 image 数据类型无效。”
- 簡單問題
ID I1 I2 I3 I4 I5
A1 (1) (2) (3) (1,2),(2,3) (1,2,3)
B1 (3) (6) (8) (3,6),(6,8) (3,6,8)
C1 (3) (6) (3,6)
...
谢谢您~
I No
(1) a
(2) b
(3) c
(6) d
(8) e
(1,2) f
(2,3) g
(3,6) h
(6,8) i
(1,2,3) j
(3,6,8) k
if object_id('t1') <>'' drop table t1
if object_id('t2') <>'' drop table t2create table t1(c1 varchar(10),c2 varchar(10),c3 varchar(10),c4 varchar(10),c5 varchar(10),c6 varchar(10),c7 varchar(10))
insert into t1
select 'A1', '(1)' , '(2)', '(3)', '(1,2)','(2,3)', '(1,2,3)'
union all select 'B1', '(3)', '(6)', '(8)', '(3,6)','(6,8)', '(3,6,8)'
union all select 'C1', '(3)', '(6)', '', '(3,6)','',''create table t2 (c1 varchar(10),c2 varchar(10))
insert into t2
select '(1)', 'a'
union all select '(2)', 'b'
union all select '(3)', 'c'
union all select '(6)', 'd'
union all select '(8)', 'e'
union all select '(1,2)', 'f'
union all select '(2,3)', 'g'
union all select '(3,6)', 'h'
union all select '(6,8)', 'i'
union all select '(1,2,3)', 'j'
union all select '(3,6,8)', 'k'
select * from t1
select * from t2
----函數
create function func_s(@c varchar(10))
returns varchar(10)
as
begin
declare @e varchar(10)
set @e=''
select @e=c2 from t2 where c1=@c
return(@e)
end
select * from t1
/*結果:C1 C2 C3 C4 C5 C6 C7
-------------------------------------------------------------------------------------
A1 (1) (2) (3) (1,2) (2,3) (1,2,3)
B1 (3) (6) (8) (3,6) (6,8) (3,6,8)
C1 (3) (6) (3,6) */
select c1,dbo.func_s(c2) as c2 ,dbo.func_s(c3) as c3,dbo.func_s(c4) as c4,dbo.func_s(c5) as c5,dbo.func_s(c6) as c6,dbo.func_s(c7) as c7 from t1
/*
結果:
C1 C2 C3 C4 C5 C6 C7
-------------------------------------------------------------------------------------
A1 a b c f g j
B1 c d e h i k
C1 c d h
*/
表t1不对,I4不可以拆
一列一列UPDATE不就行了:
*/--原始数据:@A
declare @A table(ID varchar(2),I1 varchar(3),I2 varchar(3),I3 varchar(5),I4 varchar(11),I5 varchar(7))
insert @A
select 'A1','(1)','(2)','(3)','(1,2),(2,3)','(1,2,3)' union all
select 'B1','(3)','(6)','(8)','(3,6),(6,8)','(3,6,8)' union all
select 'C1','(3)','(6)','','(3,6)',''
--原始数据:@B
declare @B table(I varchar(7),No varchar(1))
insert @B
select '(1)','a' union all
select '(2)','b' union all
select '(3)','c' union all
select '(6)','d' union all
select '(8)','e' union all
select '(1,2)','f' union all
select '(2,3)','g' union all
select '(3,6)','h' union all
select '(6,8)','i' union all
select '(1,2,3)','j' union all
select '(3,6,8)','k'update a set a.I1=b.No from @A a join @B b on a.I1=b.I
update a set a.I2=b.No from @A a join @B b on a.I2=b.I
update a set a.I3=b.No from @A a join @B b on a.I3=b.I-- I4是多个()的组合,这样处理
while exists (select 1 from @A a,@B b where charindex(b.I,a.I4)>0)
update a set a.I4=replace(I4,b.I,b.No) from @A a join @B b on charindex(b.I,a.I4)>0update a set a.I5=b.No from @A a join @B b on a.I5=b.Iselect * from @A/*
ID I1 I2 I3 I4 I5
A1 a b c f,g j
B1 c d e h,i k
C1 c d h
*/
returns varchar(50)
as
begin
if(len(@c)=0)
return ''
else
begin
declare @star int
declare @end int
declare @s varchar(50)
declare @r varchar(50)
set @star=1
set @r=''
while charindex('(',@c)>0
begin
set @s=substring(@c,1,charindex(')',@c))
select @r=@r+','+c2 from t2 where t2.c1=@s
set @star=charindex(')',@c)+1
set @c=substring(@c,@star+1,len(@c))
end
set @r=right(@r,len(@r)-1)
end
return @r
endselect c1,dbo.wsp(c2) as c2,dbo.wsp(c3) as c3,dbo.wsp(c4) as c4,dbo.wsp(c5) as c25,dbo.wsp(c6) as c6 from t1
create table t1(c1 varchar(10),c2 varchar(10),c3 varchar(10),c4 varchar(10),c5 varchar(50),c6 varchar(10))
insert into t1 select 'A1', '(1)' , '(2)', '(3)', '(1,2),(2,3)', '(1,2,3)'
union all select 'B1', '(3)', '(6)', '(8)', '(3,6),(6,8)', '(3,6,8)'
union all select 'C1', '(3)', '(6)', '', '(3,6)',''create table t2 (c1 varchar(10),c2 varchar(10))
insert into t2 select '(1)', 'a'
union all select '(2)', 'b'
union all select '(3)', 'c'
union all select '(6)', 'd'
union all select '(8)', 'e'
union all select '(1,2)', 'f'
union all select '(2,3)', 'g'
union all select '(3,6)', 'h'
union all select '(6,8)', 'i'
union all select '(1,2,3)', 'j'
union all select '(3,6,8)', 'k'
若I5也多了一个( )组合,要怎么改?谢谢!
select c1,dbo.wsp(c2) as c2,dbo.wsp(c3) as c3,dbo.wsp(c4) as c4,dbo.wsp(c5) as c25,dbo.wsp(c6) as c6 from t1
declare @A table(ID varchar(2),I1 varchar(3),I2 varchar(3),I3 varchar(5),I4 varchar(11),I5 varchar(100))
insert @A
select 'A1','(1)','(2)','(3)','(1,2),(2,3)','(1,2,3),(2,3,4)' union all
select 'B1','(3)','(6)','(8)','(3,6),(6,8)','(3,6,8)' union all
select 'C1','(3)','(6)','','(3,6)',''
--原始数据:@B
declare @B table(I varchar(7),No varchar(1))
insert @B
select '(1)','a' union all
select '(2)','b' union all
select '(3)','c' union all
select '(6)','d' union all
select '(8)','e' union all
select '(1,2)','f' union all
select '(2,3)','g' union all
select '(3,6)','h' union all
select '(6,8)','i' union all
select '(1,2,3)','j' union all
select '(3,6,8)','k' union all
select '(2,3,4)','l'--假设(2,3,4)为jupdate a set a.I1=b.No from @A a join @B b on a.I1=b.I
update a set a.I2=b.No from @A a join @B b on a.I2=b.I
update a set a.I3=b.No from @A a join @B b on a.I3=b.I-- I4是多个()的组合,这样处理
while exists (select 1 from @A a,@B b where charindex(b.I,a.I4)>0)
update a set a.I4=replace(I4,b.I,b.No) from @A a join @B b on charindex(b.I,a.I4)>0-- I5也是?I4替换成I5
while exists (select 1 from @A a,@B b where charindex(b.I,a.I5)>0)
update a set a.I5=replace(I5,b.I,b.No) from @A a join @B b on charindex(b.I,a.I5)>0select * from @A/*
ID I1 I2 I3 I4 I5
A1 a b c f,g j,l
B1 c d e h,i k
C1 c d h
*/
感谢您,语法简单明了!!!TO:pt1314917(背着灵魂漫步)
谢谢您,您的这招我学起来了!!!