表A:
ID Regions1 1,2
2 3,4
3 1,3,5表B,关联表A的Regions
ID Name
1 北京
2 上海
3 天津
4 香港
5 澳门查询结果要求如下:
AID(A.ID) RegionName
1 北京,上海
2 天津,香港
3 北京,天津,澳门明白什么意思吗?很简单,应该不难理解。
ID Regions1 1,2
2 3,4
3 1,3,5表B,关联表A的Regions
ID Name
1 北京
2 上海
3 天津
4 香港
5 澳门查询结果要求如下:
AID(A.ID) RegionName
1 北京,上海
2 天津,香港
3 北京,天津,澳门明白什么意思吗?很简单,应该不难理解。
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-23 广东深圳)/*问题描述
tba
ID classid name
1 1,2,3 西服
2 2,3 中山装
3 1,3 名裤
tbb
id classname
1 衣服
2 上衣
3 裤子我得的结果是
id classname name
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
*/-----------------------------------------------------
--sql server 2000中的写法
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3' ,'中山装')
insert into tba values(3,'1,3' ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go--第1种方法,创建函数来显示
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0
return stuff(@str,1,1,'')
end
go
select id,classid=dbo.f_hb(classid),name from tba
drop function f_hb
/*
id classid name
----------- ------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(所影响的行数为 3 行)
*/--第2种方法.update
while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))
update tba
set classid= replace(classid,tbb.id,tbb.classname)
from tbb
where charindex(tbb.id,tba.classid)>0
select * from tba
/*
ID classid name
----------- -------------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(所影响的行数为 3 行)
*/
drop table tba,tbb------------------------------------------------------------------------
--sql server 2005中先分解tba中的classid,然后再合并classname
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3' ,'中山装')
insert into tba values(3,'1,3' ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
goSELECT id , classname , name FROM
(
SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from
(
SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
) tbc , tbb where tbc.classid = tbb.id
) T
)A
OUTER APPLY
(
SELECT [classname]= STUFF(REPLACE(REPLACE((
SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from
(
SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
) tbc , tbb where tbc.classid = tbb.id
) N
WHERE id = A.id and name = A.name
FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')
)N
order by iddrop table tba,tbb/*
id classname name
----------- -------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(3 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-21 16:58:08
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[Regions] varchar(5))
insert [A]
select 1,'1,2' union all
select 2,'3,4' union all
select 3,'1,3,5'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[Name] varchar(4))
insert [B]
select 1,'北京' union all
select 2,'上海' union all
select 3,'天津' union all
select 4,'香港' union all
select 5,'澳门'
--------------开始查询--------------------------
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+','+[Name] from b where charindex(','+cast(id as varchar)+',',','+@id+',')>0
return stuff(@str,1,1,'')
end
go
select id as aid,Regions=dbo.f_hb(Regions) from a
drop function f_hb ----------------结果----------------------------
/* aid Regions
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 北京,上海
2 天津,香港
3 北京,天津,澳门(3 行受影响)
*/
--> Author : js_szy
--> Target : ★★★
--> Date : 2009-12-21 16:55:36
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] (ID int,Regions varchar(10))
insert into [ta]
select 1,'1,2' union all
select 2,'3,4' union all
select 3,'1,3,5 '
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (ID int,Name varchar(4))
insert into [tb]
select 1,'北京' union all
select 2,'上海' union all
select 3,'天津' union all
select 4,'香港' union all
select 5,'澳门'
if object_id('f_str') is not null drop function f_str
go
create function f_str(@id int)
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s+' ','')+Name
from (
select b.id,Name
from [ta] a,[tb] b where charindex(','+ltrim(b.id)+',',','+a.Regions+',')>0
)t
where id=@id
return @s
end
go select id,name=dbo.f_str(id)
from (
select b.id,Name
from [ta] a,[tb] b where charindex(','+ltrim(b.id)+',',','+a.Regions+',')>0
)t
group by id
id name
----------- ----------------------------------------------------------------------------------------------------
1 北京 北京
2 上海
3 天津 天津
4 香港(4 行受影响)
--> author:Ken Wong
--> Date: 2009-12-21 16:53:22
/*===============================================*/
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
create table [A]([ID] int,[Regions] varchar(30))
insert [A]
select 1,'1,2' union all
select 2,'3,4' union all
select 3,'1,3,5'
--> 测试数据:[B1]
if object_id('[B]') is not null drop table [B]
create table [B]([ID] int,[Name] varchar(4))
insert [B]
select 1,'北京' union all
select 2,'上海' union all
select 3,'天津' union all
select 4,'香港' union all
select 5,'澳门'select * into #temp from [A]while exists(select 1 from #temp r join [B] t on charindex(','+ltrim(t.id)+',',','+r.Regions+',')>0)
begin
update r
set Regions = replace(r.Regions,t.id,t.Name)
from #temp r join [B] t on
charindex(','+ltrim(t.id)+',',','+r.Regions+',')>0
endselect * from #tempdrop table #temp
--------------------------
1 北京,上海
2 天津,香港
3 北京,天津,澳门
--> Author : js_szy
--> Target : ★★★
--> Date : 2009-12-21 16:55:36
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] (ID int,Regions varchar(20))
insert into [ta]
select 1,'1,2' union all
select 2,'3,4' union all
select 3,'1,3,5'
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (ID int,Name varchar(4))
insert into [tb]
select 1,'北京' union all
select 2,'上海' union all
select 3,'天津' union all
select 4,'香港' union all
select 5,'澳门'if object_id('f_str') is not null drop function f_str
go
create function f_str(@id int)
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s+' ','')+Name
from (
select a.id,Name
from [ta] a,[tb] b where charindex(','+ltrim(b.id)+',',','+a.Regions+',')>0
)t
where id=@id
return @s
end
go select id,name=dbo.f_str(id)
from (
select a.id,Name
from [ta] a,[tb] b where charindex(','+ltrim(b.id)+',',','+a.Regions+',')>0
)t
group by id
id name
----------- ----------------------------------------------------------------------------------------------------
1 北京 上海
2 天津 香港
3 北京 天津 澳门(3 行受影响)