【求助】SQL Server 表链接查询问题

[灵雨飘零]

遇到这样一个问题，不知道如何写SQL语句。
表结构如下。
部门编码、部门名称、部门人员ID（中间用逗号分割）


我想通过和人员表链接，查询出一个新的数据集，查询出的结果集格式如下：
人员信息（ID或者姓名）、部门编码、部门名称


就是想把人员从字段中转换成多行显示，这样的SQL语句如何写呢？？

[xiaodongni]

--给你个列子上次面试就碰到这题目了
--测试数据
create table test2(number int)
insert into test2 values 
(1),(2),(3),(4),(5),(7),(8),
(10),(11),(13),(15)

--这是我总结的方法
declare @str varchar(8000)
select @str=' select '+REPLACE(''+number+'',',',' number union all select ') from  test1
exec(@str)
--这个效率虽然不高。但是思想很新颖。
with b as (select ROW_NUMBER()over(order by id) rn from sysobjects )  
select SUBSTRING(a.number,b.rn * 2 - 1 ,1)
from test1 a, b
where rn <= 6  
--这个想法很好。不过这个只能满足本题。如果分隔的数字只能是个位数。如果有10就不行了。
SELECT t.c.value('.','int')       
FROM(
SELECT CONVERT(XML,'<x>'+replace(number,',','</x><x>')+'</x>') AS B
    from #test1) a 
CROSS APPLY a.B.nodes('/x') T(c)  

select --t.number,
       SUBSTRING(t.number, s.number ,CHARINDEX(',',t.number+',',s.number)-s.number) as number
from test1 t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.number,s.number,1) = ','
--这上面2个方法都很好。第一种还没看懂。先发出来。XML不是很懂。等等再去查查。

原帖地址 你可以看看 http://bbs.csdn.net/topics/390884161?page=2

[灵雨飘零]

自己占沙发！！

[KeepSayingNo]

弄一个split函数，然后对部门人员ID进行拆分，最后再将这个查询结果和人员表进行关联

[xxfvba]

select Dept_Code,Content, SUBSTRING(A0188s,number,charindex(',',A0188s+',',number)-number) from T a,master..spt_values b where number>=1 and number<=LEN(A0188s) and type='P' and SUBSTRING(','+A0188s,number,1)=','

[Tiger_Zhao]

数据没有经过预处理，要拆分部门人员ID。
只有规则的数据才能发挥数据库的优势啊！

[灵雨飘零]

引用 2 楼 dotnetstudio 的回复:

弄一个split函数，然后对部门人员ID进行拆分，最后再将这个查询结果和人员表进行关联

可否给个实例语句。

[灵雨飘零]

引用 4 楼 Tiger_Zhao 的回复:

数据没有经过预处理，要拆分部门人员ID。 只有规则的数据才能发挥数据库的优势啊！

这样的数据不规则吗？？

[KeepSayingNo]

	IF object_id('tempdb..#TEMPTB1') is not null
	BEGIN
		drop table #TEMPTB1
	END
	CREATE table #TEMPTB1
	(
	    [DEPT_CODE] [int] NULL,
	    [content] [nvarchar](50) NULL,
	    [A0188s] [nvarchar](max) NULL
	)
	
	IF object_id('tempdb..#TEMPTB2') is not null
	BEGIN
		drop table #TEMPTB2
	END
	CREATE table #TEMPTB2
	(
	    [pid] [nvarchar](max) NULL
	)

declare @DEPT_CODE int
declare @content varchar(50)
declare @A0188s varchar(max)
exec('declare my_cursor1 cursor for select * from [Table_Dept]')
open my_cursor1
declare @id1 sysname
declare @id2 sysname
declare @id3 sysname
fetch next from my_cursor1 into @id1,@id2,@id3
	while(@@fetch_status= 0)
		begin	
			set @DEPT_CODE =convert(int,@id1)
			set @content =convert(varchar(50),@id2)
			set @A0188s =convert(varchar(max),@id3)
			truncate table #TEMPTB2
		     insert into #TEMPTB2 select * from Split(@A0188s,',')
		     insert into #TEMPTB1 select @DEPT_CODE,@content,pid from #TEMPTB2
			fetch next from my_cursor1 into @id1,@id2,@id3
		end
close my_cursor1
deallocate my_cursor1

select * from #TEMPTB1

[KeepSayingNo]

先把下面的函数脚本刷下

CREATE FUNCTION [dbo].[Split]   
(   
@c VARCHAR(MAX) ,   
@split VARCHAR(50)   
)   
RETURNS @t TABLE ( col VARCHAR(50) )   
AS  
BEGIN  
    WHILE ( CHARINDEX(@split, @c) <> 0 )   
        BEGIN  
            INSERT  @t( col )   
            VALUES  ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1) )   
            SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '')   
        END  
    INSERT  @t( col ) VALUES  ( @c )   
    RETURN  
END

[Tiger_Zhao]

一字段多值的数据当然不规则了

WITH tableold(DEPT_CODE,content,A0188s) AS (
    SELECT '1000',N'总务系','350,688,258' UNION ALL
    SELECT '1001',N'总经理室','2,3,4,298'
),
split (DEPT_CODE,i,istart,iend) AS (
    SELECT DEPT_CODE,
           1,
           1,
           CHARINDEX(',',A0188s,1)
      FROM tableold
    UNION ALL
    SELECT o.DEPT_CODE,
           s.i+1,
           s.iend+1,
           CHARINDEX(',',o.A0188s,s.iend+1)
      FROM split s
      JOIN tableold o
        ON o.DEPT_CODE = s.DEPT_CODE
     WHERE s.iend <> 0
)
SELECT o.DEPT_CODE,
       o.content,
       Convert(int,
               SUBSTRING(o.A0188s,
                         s.istart,
                         CASE WHEN s.iend = 0 THEN
                             len(o.A0188s)+1
                         ELSE
                             s.iend
                         END - s.istart
                        )
              ) UserID
  INTO #tableNew
  FROM split s
  JOIN tableold o
    ON o.DEPT_CODE = s.DEPT_CODE
ORDER BY DEPT_CODE,UserID

SELECT * FROM #tableNew

DEPT_CODE content      UserID
--------- -------- -----------
1000      总务系           258
1000      总务系           350
1000      总务系           688
1001      总经理室           2
1001      总经理室           3
1001      总经理室           4
1001      总经理室         298

[灵雨飘零]

引用 3 楼 xxfvba 的回复:

select Dept_Code,Content, SUBSTRING(A0188s,number,charindex(',',A0188s+',',number)-number) from T a,master..spt_values b where number>=1 and number<=LEN(A0188s) and type='P' and SUBSTRING(','+A0188s,number,1)=','

这种写法确实以前没用过，

[baoyawzw]

我写了个分享下 是直接转化为xml，在解析，简单粗暴

DECLARE @xml NVARCHAR(max),@docHandle int;

SET @xml=N'<root>';

;WITH T(DEPT_CODE,content,A0188s)
AS 
(
	SELECT 1000,'总务系','350,688,258'
	UNION ALL 
	SELECT 1001,'总经理室','2,3,4,298'
	UNION ALL 
	SELECT 1002,'人事科','231,232,233,192,225,228,304,253,254,255,256,257,331,318,321'
	UNION ALL 
	SELECT 1003,'财务科','39,40,41,42,366,684,710'
	UNION ALL 
	SELECT 1004000,'制造二班','159,161,168,169,206,273,314,335,290,164'
	UNION ALL 
	SELECT 1004003,'喷漆班','148,362,140,675'
	UNION ALL 
	SELECT 1004005,'制造一班','160,162,205,218'
	UNION ALL 
	SELECT 1004006,'钳工班','146,147,149,182'
	UNION ALL 
	SELECT 1004007,'库管系','139,113,120,134'
	UNION ALL 
	SELECT 1004008,'电装班','135,130,131'
	UNION ALL 
	SELECT 1005,'信息科','272,89'
	UNION ALL 
	SELECT 1006,'营业部','692,693,95,96'
	UNION ALL 
	SELECT 1006000,'营业一系','275,93,705'
	UNION ALL 
	SELECT 1006003,'营业二系','97,98'
	UNION ALL 
	SELECT 1007,'技术部','5,78,56'
) 
SELECT @xml=@xml+
'<main Dep="'+CAST(DEPT_CODE as NVARCHAR(10))+'"'+N' '+
				'Con="'+CAST(content AS NVARCHAR(10))+'">'+
		 '<row NeiMa="'+REPLACE(A0188s,',','"/> <row NeiMa="')+'"/> 
 </main>
' 
FROM T
SET @xml=@xml+N'</root>'
print @xml

	IF @Xml<>''
	BEGIN
		EXEC sp_xml_preparedocument @docHandle OUTPUT,@xml;
				SELECT *
				FROM OPENXML(@docHandle,'/root/main/row',2)
				WITH 
				(
					 Dep INT         '../@Dep'
					,Con NVARCHAR(50)  '../@Con'
					,NeiMa NVARCHAR(10)    '@NeiMa'
				)
		EXEC sp_xml_removedocument @docHandle 
	END