这个表是一个帖子版区表，第一列是版区id，最后一列是版区负责人，
主要列有：
版区id 版区负责人id
6 150414;1115115156
7 150414;1115115157
有一个人员表，有人员id和人员姓名，和这个表联合查询
最后想形成的表结构是
版区id 版区负责人姓名
6 张三、李四
7 张三、王五
都有什么思路可以实现呢？谢谢大家
------解决方案--------------------
拆了之后匹配完再合并起来
----------------------------------------------------------------
-- 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