日期:2014-05-16 浏览次数:20474 次
----------------------------------------------------------------
-- 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,t2
create 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