数据表取值，有点难度

Question

比如我的表结构如下员工ID  员工姓名        员工的关系
4 A              4_
1020 B              4_1020_
2181 C              4_1020_2181_
10727 D              4_1020_2181_4788_10727_
5001 E              4_1020_2181_4788_5000_5001
11326 F              4_1020_8073_8422_11326_员工的关系是无级限的上面员工A 是最上级人员员工B的上级是A [员工ID为4]员工C的上级是B [员工ID为1020]员工D的上级是 员工ID为 4788的员工 [没显示出来]员工E的上级是 员工ID为 5000的员工 [没显示出来]员工F的上级是 员工ID为 8422的员工 [没显示出来]现在的问题，我怎么样可以提出 员工的关系 那个字段里面出现的员工ID ，不能重复最终取出值   4,1020,2181,4788,10727,5000,5001,8073,8422,11326顺序不限，只要有好的算法能提取出来就可以了

Answer 1

BOM
参考：
CREATE TABLE BOM(PID INT,ID INT)
INSERT INTO BOM SELECT 801,101
INSERT INTO BOM SELECT 801,102
INSERT INTO BOM SELECT 801,103
INSERT INTO BOM SELECT 801,601
INSERT INTO BOM SELECT 601,101
INSERT INTO BOM SELECT 601,105
INSERT INTO BOM SELECT 601,501
INSERT INTO BOM SELECT 501,106
INSERT INTO BOM SELECT 501,121
GOCREATE FUNCTION F_GETROOT(@PID INT)
RETURNS INT
AS
BEGIN
    DECLARE @ID INT
    WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID)
    BEGIN
        SET @ID=@PID
        SELECT @PID=PID FROM BOM WHERE ID=@ID
    END
    RETURN @PID
END
GOSELECT PID=DBO.F_GETROOT(PID),ID FROM BOM
GO/*
PID         ID
----------- ----------- 
801         101
801         102
801         103
801         601
801         101
801         105
801         501
801         106
801         121
*/
DROP FUNCTION F_GETROOT
DROP TABLE BOM
GO
--生成测试数据
create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute  varchar(20))
insert into BOM_1 select 1 ,'A' ,'A1',1,'采购'
insert into BOM_1 select 2 ,'A' ,'A2',2,'生产'
insert into BOM_1 select 3 ,'A2','A3',3,'生产'
insert into BOM_1 select 4 ,'A2','A4',2,'采购'
insert into BOM_1 select 5 ,'A3','A5',2,'采购'
insert into BOM_1 select 6 ,'A3','A6',1,'采购'
insert into BOM_1 select 7 ,'B' ,'B1',1,'采购'
insert into BOM_1 select 8 ,'B' ,'B2',2,'生产'
insert into BOM_1 select 9 ,'B2','B3',3,'生产'
insert into BOM_1 select 10,'B2','B4',2,'采购'
insert into BOM_1 select 11,'B3','B5',2,'采购'
insert into BOM_1 select 12,'B3','B6',2,'采购'
go
   --创建用户定义函数，用于取每个父节点下子节点的采购配置信息
create function f_stock(@bom_head varchar(20))
returns @t table(bom varchar(20),number int)
as
begin 
    declare @level int
    declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int)
    set @level=1    if exists(select 1 from BOM_1 where bom_head=@bom_head)    
    insert into @a 
    select bom_child,number,products_attribute,@level 
    from BOM_1 
    where bom_head=@bom_head
    
    while exists(select 1 from @a where [level]=@level and products_attribute='生产')
    begin
        set @level=@level+1
        insert into @a(bom,number,products_attribute,[level])
        select a.bom_child,a.number,a.products_attribute,@level 
        from BOM_1 a,@a b
        where a.bom_head=b.bom and b.[level]=@level-1
    end
    
    insert into @t(bom,number) select bom,number from @a where products_attribute='采购'
    return
end
go
--执行调用，取父节点'A'一个标准配置分解的采购信息及数量
select * from dbo.f_stock('A')
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i int
    set @i = 1
    insert into @t select ID,PID,@i from BOM where PID = @ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i 
        from 
            BOM a,@t b 
        where 
            a.PID=b.ID and b.Level = @i-1
    end
    return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果
/*
ID
----
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM创建用户定义函数,每个子节点de父节点的信息
--生成测试数据
create table BOM(ID int,parentID int,sClassName varchar(10))
insert into BOM values(1,0,'1111'      )
insert into BOM values(2,1,'1111_1'    )
insert into BOM values(3,2,'1111-1-1'  )
insert into BOM values(4,3,'1111-1-1-1') 
insert into BOM values(5,1,'1111-2'    )go--创建用户定义函数,每个子节点de父节点的信息
create function f_getParent(@ID int)
returns varchar(40)
as
begin
    declare @ret varchar(40)    while exists(select 1 from BOM where ID=@ID and parentID<>0)
    begin
        select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'')
        from
            BOM a,BOM b
        where
            a.ID=@ID and b.ID=a.parentID
    end
    
    set @ret=stuff(@ret,1,1,'')
    return @ret
end
go--执行查询
select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOM
go--输出结果
/*
ID          parentID                                 
----------- ---------------------------------------- 
1           
2           1
3           1,2
4           1,2,3
5           1   
*/--删除测试数据
drop function f_getParent
drop table BOM
go

Answer 2

select distinct 员工ID from 表 a where exists
(select 1 from 表 where charindex(ltrim(a.员工ID),员工的关系)>0)

Answer 3

--先把工的关系按'-'分拆开，然后取DISTINCT id就可以了。
--分拆员工的关系 可以用这个：
分拆列值 有表tb, 如下: 
id          value 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
----------- -------- 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 1. 旧的解决方法(sql server 2000) 
 
select a.id, substring(a.[value], b.number, charindex(',', a.[value] + ',', b.number) - b.number) 
FROM tb a, master..spt_values  b 
WHERE b.type='p' and substring(',' + a.[value],b.number, 1) = ',' 
2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 
go 
SELECT a.id, b.value 
FROM( 
    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLaCE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb 
)a 
OUTER aPPLY( 
    SELECT value = N.v.value('.', 'varchar(100)') FROM a.[value].nodes('/root/v') N(v) 
)b DROP TabLE tb /* 
id          value 
----------- ------------------------------ 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc (5 行受影响) 
*/

Answer 4

DECLARE @TB TABLE([员工ID] VARCHAR(5), [员工姓名] VARCHAR(1), [员工的关系] VARCHAR(26))
INSERT @TB 
SELECT '4', 'A', '4_' UNION ALL 
SELECT '1020', 'B', '4_1020_' UNION ALL 
SELECT '2181', 'C', '4_1020_2181_' UNION ALL 
SELECT '10727', 'D', '4_1020_2181_4788_10727_' UNION ALL 
SELECT '5001', 'E', '4_1020_2181_4788_5000_5001' UNION ALL 
SELECT '11326', 'F', '4_1020_8073_8422_11326_'SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTSDECLARE @SQL VARCHAR(100)
SET @SQL=''SELECT @SQL=@SQL+','+RTRIM(员工的关系)
FROM (
SELECT DISTINCT CAST(员工的关系 AS INT) AS 员工的关系
FROM (
SELECT SUBSTRING(员工的关系, ID, CHARINDEX('_', 员工的关系+'_', ID)-ID) AS 员工的关系
FROM @TB, #
WHERE CHARINDEX('_', '_'+员工的关系, ID)=ID
) TA
WHERE 员工的关系<>''
)TB
ORDER BY 员工的关系SELECT STUFF(@SQL,1,1,'')DROP TABLE #
/*
4,1020,2181,4788,5000,5001,8073,8422,10727,11326
*/

Answer 5

DECLARE @TB TABLE([员工ID] int, [员工姓名] VARCHAR(1), [员工的关系] VARCHAR(26))
INSERT @TB 
SELECT '4', 'A', '4_' UNION ALL 
SELECT '1020', 'B', '4_1020_' UNION ALL 
SELECT '2181', 'C', '4_1020_2181_' UNION ALL 
SELECT '10727', 'D', '4_1020_2181_4788_10727_' UNION ALL 
SELECT '5001', 'E', '4_1020_2181_4788_5000_5001' UNION ALL 
SELECT '11326', 'F', '4_1020_8073_8422_11326_'declare @id varchar(8000)
set @id=''select   @id=@id+','+ltrim(员工ID) from @TB a where exists
(select 1 from @TB where charindex(ltrim(a.员工ID),员工的关系)>0)
 select @id--,4,1020,2181,10727,5001,11326

Answer 6

仅供参考--树型结构处理之双编号（广度深度排序）
if OBJECTPROPERTY(object_id('tb'),'isusertable')<>0 
  drop table tb
create table tb(ybh nvarchar(10),ebh nvarchar(10),beizhu nvarchar(1000))
insert tb
select '0001',null,'云南省'
union all select '0002','0001','昆明市'
union all select '0003','0001','昭通市'
union all select '0009','0001','大理市'
union all select '0008',null,'四川省'
union all select '0004',null,'贵州省'
union all select '0005','0002','五华区'
union all select '0007','0002','水富县'
union all select '0006','0005','西园路192号'
union all select '0010','0006','ddsfsf'
union all select '0011','0010','dddd'
union all select '0015','0007','两碗乡'
union all select '0013','0015','两碗村'
union all select '0012','0013','某跨国集团dddd'
union all select '0014','0008','成都市'--select * from tb
--广度排序(先显示第一层节点，再显示第二次节点......)
declare @level_tb table(bh nvarchar(10),level int)
declare @level int
set @level=0
insert @level_tb(bh,level)
select ybh,@level from tb where ebh is null
while @@ROWCOUNT>0
  begin
    set @level=@level+1
    insert @level_tb(bh,level)
      select ybh,@level 
        from tb a,@level_tb b
        where a.ebh=b.bh 
              and b.level=@level-1
  end 
  select a.*,b.* from tb a,@level_tb b where a.ybh=b.bh order by level
  
  
  --深度排序
   declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt(ybh,ebh,level)
  select ybh,ybh,@level from tb where ebh is null
  while @@ROWCOUNT>0
  begin 
          set @level=@level+1
          insert @level_tt(ybh,ebh,level)
          select a.ybh,b.ebh+a.ybh,@level
            from tb a,@level_tt b
            where a.ebh=b.ybh and b.level=@level-1
 end
select space(b.level*2)+'----'+a.beizhu,a.*,b.*
  from tb a,@level_tt b
  where a.ybh=b.ybh
  order by b.ebh

Answer 7

"提出 员工的关系 那个字段里面出现的员工ID " 不就是"员工ID"字段的值吗？如果是的话，请示一下下面的语句，看是否能满足你的要求！
select top 1 '员工ID'=stuff((select ','+[员工ID] from Table1 for xml path('')),1,1,'') from Table1

Answer 8

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-08-06 10:11:51
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([员工ID] int,[员工姓名] nvarchar(1),[员工的关系] nvarchar(26))
Insert tb
Select 4,'A','4_' union all
Select 1020,'B','4_1020_' union all
Select 2181,'C','4_1020_2181_' union all
Select 10727,'D','4_1020_2181_4788_10727_' union all
Select 5001,'E','4_1020_2181_4788_5000_5001' union all
Select 11326,'F','4_1020_8073_8422_11326_'
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select
a.[员工姓名],
[员工的关系]=substring(left(a.[员工的关系],len(a.[员工的关系])-1), b.number, charindex('_', left(a.[员工的关系],len(a.[员工的关系])-1) + '_', b.number) - b.number)
from tb a,master..spt_values b 
where b.type='p' 
and substring('_' + left(a.[员工的关系],len(a.[员工的关系])-1),b.number,1) = '_' 
),t1 as
(
select distinct 员工的关系
from t a
where exists(
select 1
from t b
where b.[员工姓名]=a.[员工姓名]
  and exists(
  select 1
  from t c
  where c.员工的关系=b.员工的关系))
)
select stuff((select ','+员工的关系 from t1 for xml path('')),1,1,'') as 员工的关系
/*
员工的关系
---------------------------------------------------------
8422,5000,10727,4,500,2181,4788,8073,11326,1020(1 行受影响)
*/

Answer 9

高手很多哦。。我现在没时间看还要过一下子来看字段简化一下
员工ID   empid 
员工关系  orderby
这个的表名  Sys_GradeCompute