请问怎样用函数或存储过程得到“员工姓名”字段？

Question

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-22 17:19:13
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
-- Dec 28 2012 20:23:12 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[emp]
if object_id('[emp]') is not null drop table [emp]
go 
create table [emp]([员工id] varchar(3),[姓名] varchar(4))
insert [emp]
select '001','张三' union all
select '002','李四' union all
select '003','XXX'
--> 测试数据：[app]
if object_id('[app]') is not null drop table [app]
go 
create table [app]([应用id] varchar(6),[应用名] varchar(5),[维护员工id] varchar(11))
insert [app]
select 'APP001','应用a','001,002,003' union all
select 'APP002','应用b','002,003'
--------------开始查询--------------------------IF OBJECT_ID('tempdb..#ym','u')IS NOT NULL 
DROP TABLE #ym
SELECT a.姓名,b.* INTO #ym
FROM [emp] a LEFT JOIN (
select
    [应用id], [应用名],
    SUBSTRING([维护员工id],number,CHARINDEX(',',[维护员工id]+',',number)-number) as [维护员工id] 
from
    [app] a,master..spt_values 
where
    number >=1 and number<=len([维护员工id])  
    and type='p' 
    and substring(','+[维护员工id],number,1)=',')b ON a.[员工id]=b.维护员工id
  
select a.应用id,a.应用名,
stuff((select ','+维护员工id from #ym b 
       where b.应用id=a.应用id and b.应用名=a.应用名 
       for xml path('')),1,1,'') '维护员工id',
   stuff((select ','+姓名 from #ym b 
       where b.应用id=a.应用id and b.应用名=a.应用名 
       for xml path('')),1,1,'') '维护员工姓名'
from #ym a
group by a.应用id,a.应用名
----------------结果----------------------------
/* 
应用id   应用名   维护员工id                                                                                                                                                                                                                                                           维护员工姓名
------ ----- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP001 应用a   001,002,003                                                                                                                                                                                                                                                      张三,李四,XXX
APP002 应用b   002,003                                                                                                                                                                                                                                                          李四,XXX*/

Answer 1

if object_id('[emp]') is not null drop table [emp]
go 
create table [emp]([员工id] varchar(3),[姓名] varchar(4))
insert [emp]
select '001','张三' union all
select '002','李四' union all
select '003','XXX'if object_id('[app]') is not null drop table [app]
go 
create table [app]([应用id] varchar(6),[应用名] varchar(5),[维护员工id] varchar(11))
insert [app]
select 'APP001','应用a','001,002,003' union all
select 'APP002','应用b','002,003'
select distinct
       [应用id],
       [应用名],
       [维护员工id],
       stuff((select ','+e.[姓名] from emp e 
              where charindex(','+e.[员工id]+',',','+t.维护员工id+',')>0
  for xml path('')
),1,1,'') as 维护员工姓名
from app t
/*
应用id 应用名 维护员工id 维护员工姓名
APP001 应用a 001,002,003 张三,李四,XXX
APP002 应用b 002,003 李四,XXX
*/

Answer 2

sql server 2000的 for xml path语法错误

Answer 3

这个for xml path是2005以后才有的。下面是适合2000的方法：if object_id('[emp]') is not null drop table [emp]
go 
create table [emp]([员工id] varchar(3),[姓名] varchar(4))
insert [emp]
select '001','张三' union all
select '002','李四' union all
select '003','XXX'if object_id('[app]') is not null drop table [app]
go 
create table [app]([应用id] varchar(6),[应用名] varchar(5),[维护员工id] varchar(11))
insert [app]
select 'APP001','应用a','001,002,003' union all
select 'APP002','应用b','002,003'
goif OBJECT_ID('mergeStr') is not null
   drop function dbo.mergeStr
go--建立一个函数
create function dbo.mergeStr(@id varchar(100))
returns varchar(100)
as
begindeclare @str varchar(100)set @str = '';select @str = @str + ','+e.[姓名] 
from emp e 
where charindex(','+e.[员工id]+',',','+@id+',')>0return stuff(@str,1,1,'');endgo
select distinct
       [应用id],
       [应用名],
       [维护员工id],
       dbo.mergeStr(t.维护员工id) as 维护员工姓名
from app t
/*
应用id 应用名 维护员工id 维护员工姓名
APP001 应用a 001,002,003 张三,李四,XXX
APP002 应用b 002,003 李四,XXX
*/

Answer 4

--> 测试数据：[emp]
if object_id('[emp]') is not null drop table [emp]
go 
create table [emp]([员工id] varchar(3),[姓名] varchar(4))
insert [emp]
select '001','张三' union all
select '002','李四' union all
select '003','XXX'
--> 测试数据：[app]
if object_id('[app]') is not null drop table [app]
go 
create table [app]([应用id] varchar(6),[应用名] varchar(5),[维护员工id] varchar(11))
insert [app]
select 'APP001','应用a','001,002,003' union all
select 'APP002','应用b','002,003' 
go
if object_id('F_Str') is not null
    drop function F_Str
go
create function F_Str(@Col1 NVARCHAR(100))
returns nvarchar(100)
as
begin
    declare @S nvarchar(100)
SELECT @S=isnull(@S+',','')+b.姓名 
FROM emp B
where  CHARINDEX(','+B.员工id+',',','+@Col1+',')>0 
    return @S
end
go
Select DISTINCT  A.*,姓名=dbo.F_Str([维护员工id]) from APP A 
 
go
 /*
 应用id   应用名   维护员工id      姓名
------ ----- ----------- ----------------------------------------------------------------------------------------------------
APP001 应用a   001,002,003 张三,李四,XXX
APP002 应用b   002,003     李四,XXX
 */

Answer 5

这样有个维护人员顺序问题，如维护人员
'003,001,002' ，出来应该是“XXX,张三,李四”，但以上函数出来还是“张三,李四,XXX ”，这样怎么搞？

Answer 6

把函数修改了一下，试试这个：
if object_id('[emp]') is not null drop table [emp]
go 
create table [emp]([员工id] varchar(3),[姓名] varchar(4))
insert [emp]
select '001','张三' union all
select '002','李四' union all
select '003','XXX'
 
if object_id('[app]') is not null drop table [app]
go 
create table [app]([应用id] varchar(6),[应用名] varchar(5),[维护员工id] varchar(11))
insert [app]
select 'APP001','应用a','003,001,002' union all
select 'APP002','应用b','002,003'
go
 --1.函数
if OBJECT_ID('mergeStr') is not null
   drop function dbo.mergeStr
gocreate function dbo.mergeStr
(
@s varchar(100),     --要分拆的字符串
@split varchar(10)    --分隔字符
) 
returns varchar(100)begin   
  declare @len int
  declare @str varchar(100)
  
  set @len = LEN(@split)      --分隔符不一定就是一个字符，可能是2个字符
  set @str = ''
  set @s = @s + ','
  
  while CHARINDEX(@split,@s) >0
  begin
    
    select @str = @str +@split+ [姓名]
    from Emp
    where [员工id]= left(@s,charindex(@split,@s) - 1)

set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'')    --覆盖：字符串以及分隔符
  end
  
  return stuff(@str,1,1,'')  --返回临时表
end
go  select distinct
       [应用id],
       [应用名],
       [维护员工id],
       dbo.mergeStr(t.维护员工id,',') as 维护员工姓名
from app t
/*
应用id 应用名 维护员工id 维护员工姓名
APP001 应用a 003,001,002 XXX,张三,李四
APP002 应用b 002,003 李四,XXX
*/