拆了之后匹配完再合并起来---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-17 11:18:55 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([版区id] int,[版区负责人id] nvarchar(34)) insert [huang] select 6,'150414;1115115156' union all select 7,'150414;1115115157' --------------生成数据-------------------------- select [版区id], SUBSTRING([版区负责人id],number,CHARINDEX(';',[版区负责人id]+';',number)-number) as [版区负责人id] from [huang] a,master..spt_values where number >=1 and number<=len([版区负责人id]) and type='p' and substring(';'+[版区负责人id],number,1)=';' ----------------结果---------------------------- /* 版区id 版区负责人id ----------- ---------------------------------- 6 150414 6 1115115156 7 150414 7 1115115157 */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-06-17 11:35:40 -- Version: -- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) -- Feb 10 2012 19:13:17 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing 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]([版区id] int,[版区负责人id] varchar(17)) insert [a] select 6,'150414;1115115156' union all select 7,'150414;1115115157' --> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([人员id] int,[人员姓名] varchar(4)) insert [b] select 150414,'张三' union all select 1115115156,'李四' union all select 1115115157,'王五' --------------开始查询-------------------------- select 版区id, 版区负责人id=stuff((select ';'+人员姓名 from a ,b where 版区id=t.版区id and charindex(';'+ltrim(b.人员id)+';',';'+a.版区负责人id+';')>0 for xml path('')),1,1,'') from a as t group by 版区id ----------------结果---------------------------- /* 版区id 版区负责人id ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 6 张三;李四 7 张三;王五(2 行受影响) */
-- 我也来凑热闹。。 --> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([版区id] int,[版区负责人id] varchar(17)) insert [a] select 6,'150414;1115115156' union all select 7,'150414;1115115157' --> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([人员id] int,[人员姓名] varchar(4)) insert [b] select 150414,'张三' union all select 1115115156,'李四' union all select 1115115157,'王五'--------------开始查询-------------------------- ;WITH t AS ( SELECT a.[版区id],r1.Value userId FROM a CROSS APPLY( SELECT * FROM dbo.[Split](a.[版区负责人id],';') AS s )r1 )SELECT t.[版区id] ,版区负责人姓名=stuff(( SELECT '、'+lb.[人员姓名] FROM t lt INNER JOIN b lb ON lt.userId=lb.[人员id] WHERE lt.[版区id]=t.[版区id] FOR XML PATH('') ),1,1,'') FROM t GROUP BY t.[版区id]
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-17 11:18:55
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([版区id] int,[版区负责人id] nvarchar(34))
insert [huang]
select 6,'150414;1115115156' union all
select 7,'150414;1115115157'
--------------生成数据--------------------------
select
[版区id],
SUBSTRING([版区负责人id],number,CHARINDEX(';',[版区负责人id]+';',number)-number) as [版区负责人id]
from
[huang] a,master..spt_values
where
number >=1 and number<=len([版区负责人id])
and type='p'
and substring(';'+[版区负责人id],number,1)=';'
----------------结果----------------------------
/*
版区id 版区负责人id
----------- ----------------------------------
6 150414
6 1115115156
7 150414
7 1115115157
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-17 11:35:40
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing 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]([版区id] int,[版区负责人id] varchar(17))
insert [a]
select 6,'150414;1115115156' union all
select 7,'150414;1115115157'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([人员id] int,[人员姓名] varchar(4))
insert [b]
select 150414,'张三' union all
select 1115115156,'李四' union all
select 1115115157,'王五'
--------------开始查询--------------------------
select
版区id,
版区负责人id=stuff((select ';'+人员姓名 from a ,b where 版区id=t.版区id and charindex(';'+ltrim(b.人员id)+';',';'+a.版区负责人id+';')>0 for xml path('')),1,1,'')
from a as t
group by
版区id
----------------结果----------------------------
/* 版区id 版区负责人id
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 张三;李四
7 张三;王五(2 行受影响)
*/
-- 我也来凑热闹。。 --> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([版区id] int,[版区负责人id] varchar(17))
insert [a]
select 6,'150414;1115115156' union all
select 7,'150414;1115115157'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([人员id] int,[人员姓名] varchar(4))
insert [b]
select 150414,'张三' union all
select 1115115156,'李四' union all
select 1115115157,'王五'--------------开始查询--------------------------
;WITH t AS (
SELECT a.[版区id],r1.Value userId FROM a
CROSS APPLY(
SELECT * FROM dbo.[Split](a.[版区负责人id],';') AS s
)r1
)SELECT t.[版区id]
,版区负责人姓名=stuff((
SELECT '、'+lb.[人员姓名] FROM t lt
INNER JOIN b lb ON lt.userId=lb.[人员id]
WHERE lt.[版区id]=t.[版区id]
FOR XML PATH('')
),1,1,'')
FROM t GROUP BY t.[版区id]
------------结果
/*
版区id 版区负责人姓名
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 张三、李四
7 张三、王五(2 行受影响)
*/
-- dbo.Split方法见-- http://blog.csdn.net/feiazifeiazi/article/details/17242355