请教个问题,我在数据库中表tableA放的是
A1|A2
1|张三
2|李四
3|王五
.........
88|周强
之类数据,A1中数据小于99.
另一个表tableB中
B1|B2|B3
1 |1 |010203
2 |8 |03010588
B3里的值,是和表tableA中的A1字段对应的,我现在想要将表tableB中显示为
B1|B2|B3
1 |1 |张三李四王五
2 |8 |王五张三刘奇周强
tableB中B3字段每两位表示一个人,怎么才能做到?
A1|A2
1|张三
2|李四
3|王五
.........
88|周强
之类数据,A1中数据小于99.
另一个表tableB中
B1|B2|B3
1 |1 |010203
2 |8 |03010588
B3里的值,是和表tableA中的A1字段对应的,我现在想要将表tableB中显示为
B1|B2|B3
1 |1 |张三李四王五
2 |8 |王五张三刘奇周强
tableB中B3字段每两位表示一个人,怎么才能做到?
go
select 1 as A1, '张三' as A2
into tableA
union select 2, '李四'
union select 3, '王五'
union select 5, '刘奇'
union select 88, '周强'if object_id('tableB') is not null drop table tableB
go
select 1 as B1, 1 as B2, '010203' as B3
into tableB
union select 2, 8, '03010588'--自定义函数
if object_id('testfunc') is not null drop function testfunc
go
create function testfunc(@B3 varchar(20)) returns varchar(100)
as
begin
declare @s1 varchar(20), @s2 varchar(100)
set @s1 = @B3
set @s2 = ''
while len(@s1) > 0
begin
select @s2 = @s2 + A2 from tableA where A1 = cast(left(@s1, 2) as int)
set @s1 = right(@s1, len(@s1) - 2)
end
return(@s2)
end
go
select B1, B2, dbo.testfunc(B3) as B3 from tableB
/*
B1 B2 B3
1 1 张三李四王五
2 8 王五张三刘奇周强
*/drop table tableA
drop table tableB
drop function testfunc
--------------------------
if object_id('tableA') is not null drop table tableA
go
select 1 as A1, '张三' as A2
into tableA
union select 2, '李四'
union select 3, '王五'
union select 5, '刘奇'
union select 88, '周强'if object_id('tableB') is not null drop table tableB
go
select 1 as B1, 1 as B2, '010203' as B3
into tableB
union select 2, 8, '03010588'if object_id('testfunc') is not null drop function testfunc
go
create function testfunc(@B3 varchar(20)) returns varchar(100)
as
begin
declare @s varchar(100)
set @s = ''
select @s = @s + A2
from (select *, charindex(right(cast(100 + A1 as varchar(3)), 2) , @B3) as A3
from tableA) a
where A3 > 0 and A3 % 2 = 1
return(@s)
end
go
select B1, B2, dbo.testfunc(B3) as B3 from tableB
/*
B1 B2 B3
1 1 张三李四王五
2 8 张三王五刘奇周强
*/drop table tableA
drop table tableB
drop function testfunc