---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-03-13 10:32:12 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([CityID] int,[Name] varchar(4)) insert [a] select 1,'深圳' union all select 2,'广州' union all select 3,'北京' --> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([ID] int,[CityID] varchar(5)) insert [b] select 1,'1,2' union all select 2,'1,2,3' --------------开始查询-------------------------- SELECT *, stuff((select ','+[name] from a where CHARINDEX(','+LTRIM(a.CityID)+',',','+b.CityID+',')>0 for xml path('')), 1, 1, '') AS name FROM b----------------结果---------------------------- /* ID CityID ----------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 1,2 深圳,广州 2 1,2,3 深圳,广州,北京(2 行受影响) */
--drop table t1,t2create table t1(CityID int,Name varchar(10))insert into t1 select 1 ,'深圳' union all select 2 ,'广州' union all select 3 ,'北京'
create table t2(ID int, CityID varchar(20))insert into t2 select 1 ,'1,2,' union all select 2 ,'1,3,' go;with t as ( select t2.id,t2.CityID,t1.Name from t1,t2 where ','+t2.CityID like '%,'+cast(t1.CityID as varchar)+',%' )select distinct tt.ID,tt.CityID, stuff((select ','+name from t where t.cityid = tt.cityid for xml path('')),1,1,'') name from t tt /* ID CityID name 1 1,2, 深圳,广州 2 1,3, 深圳,北京 */
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-13 10:32:12
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([CityID] int,[Name] varchar(4))
insert [a]
select 1,'深圳' union all
select 2,'广州' union all
select 3,'北京'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ID] int,[CityID] varchar(5))
insert [b]
select 1,'1,2' union all
select 2,'1,2,3'
--------------开始查询--------------------------
SELECT
*,
stuff((select ','+[name] from a where CHARINDEX(','+LTRIM(a.CityID)+',',','+b.CityID+',')>0 for xml path('')), 1, 1, '') AS name
FROM
b----------------结果----------------------------
/* ID CityID
----------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1,2 深圳,广州
2 1,2,3 深圳,广州,北京(2 行受影响)
*/
select 1 ,'深圳' union all
select 2 ,'广州' union all
select 3 ,'北京'
create table t2(ID int, CityID varchar(20))insert into t2
select 1 ,'1,2,' union all
select 2 ,'1,3,'
go;with t
as
(
select t2.id,t2.CityID,t1.Name
from t1,t2
where ','+t2.CityID like '%,'+cast(t1.CityID as varchar)+',%'
)select distinct
tt.ID,tt.CityID,
stuff((select ','+name from t
where t.cityid = tt.cityid
for xml path('')),1,1,'') name
from t tt
/*
ID CityID name
1 1,2, 深圳,广州
2 1,3, 深圳,北京
*/