表A:v_name v_xq
张三 ,31,
李四 ,45,52,表B:id name
27 aa
31 cc
45 re
52 ds请问:怎样写sql语句,使它输出后这样显示:
v_name name
张三 cc
李四 re,ds
张三 ,31,
李四 ,45,52,表B:id name
27 aa
31 cc
45 re
52 ds请问:怎样写sql语句,使它输出后这样显示:
v_name name
张三 cc
李四 re,ds
go
--> -->
if not object_id('A') is null
drop table A
Go
Create table A([v_name] nvarchar(2),[v_xq] nvarchar(100))
Insert A
select N'张三',N',31,' union all
select N'李四',N',45,52,'
Go
--> -->
if not object_id('B') is null
drop table B
Go
Create table B([id] int,[name] nvarchar(10))
Insert B
select 27,N'aa' union all
select 31,N'cc' union all
select 45,N're' union all
select 52,N'ds'
Go
if object_id('S_str') is not null
drop function S_str
go
create function S_str(@v_xq nvarchar(100))
returns nvarchar(100)
as
begin
select @v_xq=replace(@v_xq,','+rtrim([id])+',',','+[name]+',') from B where @v_xq like '%,'+rtrim([id])+',%'
return @v_xq
end
goselect [v_name],dbo.S_str([v_xq])[v_xq] from A
v_name v_xq
------ ----------------------------------------------------------------------------------------------------
张三 ,cc,
李四 ,re,ds,(2 個資料列受到影響)
--> 生成测试数据: #tb1
IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1
CREATE TABLE #tb1 (v_name VARCHAR(4),v_xq VARCHAR(20))
INSERT INTO #tb1
SELECT '张三',',31' UNION ALL
SELECT '李四',',45,52'
--> liangCK小梁 于2008-10-16
--> 生成测试数据: #tb2
IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2
CREATE TABLE #tb2 (id INT,name VARCHAR(2))
INSERT INTO #tb2
SELECT 27,'aa' UNION ALL
SELECT 31,'cc' UNION ALL
SELECT 45,'re' UNION ALL
SELECT 52,'ds'--SQL查询如下:SELECT v_name,
CAST(STUFF((SELECT ','+name
FROM #tb2
WHERE CHARINDEX(','+RTRIM(id)+',',a.v_xq+',')>0
FOR XML PATH('')),1,1,'') AS VARCHAR(20)) v_xq
FROM #tb1 AS a/*
v_name v_xq
------ --------------------
张三 cc
李四 re,ds(2 行受影响)*/
returns varchar(20)
as
begin
declare
@return varchar(20)
set @return = ''
select @return = name from B where id in (substring(ltrim(rtrim(@xq)),2,len(ltrim(rtrim(@xq)))-1))
return @return
end
select v_name,dbo.getvalue(v_xq) from a
go
--> -->
if not object_id('A') is null
drop table A
Go
Create table A([v_name] nvarchar(2),[v_xq] nvarchar(100))
Insert A
select N'张三',N',31,' union all
select N'李四',N',45,52,'
Go
--> -->
if not object_id('B') is null
drop table B
Go
Create table B([id] int,[name] nvarchar(10))
Insert B
select 27,N'aa' union all
select 31,N'cc' union all
select 45,N're' union all
select 52,N'ds'
Go
if object_id('S_str') is not null
drop function S_str
go
create function S_str(@v_xq nvarchar(100))
returns nvarchar(100)
as
begin
select @v_xq=replace(@v_xq,','+rtrim([id])+',',','+[name]+',') from B where @v_xq like '%,'+rtrim([id])+',%'
return substring(@v_xq,2,len(@v_xq)-2)--去掉前后引號
end
goselect [v_name],dbo.S_str([v_xq])[v_xq] from Av_name v_xq
------ ----------------------------------------------------------------------------------------------------
张三 cc
李四 re,ds(2 個資料列受到影響)
--测试数据
create table 表A(v_name nvarchar(200),v_xq nvarchar(200))
insert into 表A
select '张三',',31,' union all
select '李四',',45,52,'create table 表B(id nvarchar(20),[name] nvarchar(20))
insert into 表B
select '27','aa' union all
select '31','cc' union all
select '45','re' union all
select '52','ds'
select v_name,name =
stuff((select ','+name from 表b
where charindex(','+id+',',a.v_xq)>0
for xml path('')),1,1,'')
from 表A a
select '张三' fname,'31' xq into #A union
select '李四','45,52'select '27' [id] ,'aa' [name] into #B union
select '31','cc' union
select '45','re' union
select '52','ds'
while exists(select * from #a a
left join #b b on a.xq like'%'+b.[id]+'%'
where b.[name] is not null)
begin
update a set xq=replace(a.xq,b.[id],b.[name]) from #a a
left join #b b on a.xq like'%'+b.[id]+'%'
where b.[name] is not null
endselect * from #a
drop table #a,#b