字符串拆分函数\树形结构函数

 
USE [mytest]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_get_Split]    Script Date: 10/11/2010 09:01:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[fn_get_Split]
(
 @SplitString varchar(4000), -- 如果要传入NText类型，下面需要相应的修改，注释行为NText下同
 @Separator varchar(2) = ','-- NVarChar(2) = N','
)
RETURNS @SplitStringsTable TABLE
(
 [id] int identity(1,1),
 [value] varchar(8000) -- NVarChar(4000)
)
AS
BEGIN
    DECLARE @CurrentIndex int
    DECLARE @NextIndex int
    DECLARE @ReturnText varchar(8000)-- NVarChar(4000)
    SELECT @CurrentIndex=1
    WHILE(@CurrentIndex<=datalength(@SplitString)) -- DATALENGTH(@SplitString)/2
    BEGIN
        SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex)
        IF(@NextIndex=0 OR @NextIndex IS NULL)
            SELECT @NextIndex=datalength(@SplitString)+1--DATALENGTH(@SplitString)/2
        
        SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex)

        INSERT INTO @SplitStringsTable([value])
        VALUES(@ReturnText)
        
        SELECT @CurrentIndex=@NextIndex+1
    END
    RETURN
END

 GO

 

 

create table tb  (ID int, YHM char(2),DepartMC nvarchar(120), JB int)
go
insert into tb  
select 3 ,'sa',' 市局', null  union all
select 4 ,'sa',' 流转税处', 3 union all
select 5 ,'sa',' 信息中心', 3 union all
select 14 ,'sa',' 服务一科', 10 union all
select 16 ,'sa',' 服务四科', 10 union all 
select 9 ,'sa',' 分局', null union all
select 10 ,'sa',' 第二直属局', 9 union all
select 11 ,'sa',' 估价科', 10 union all
select 12 ,'sa',' 估价机构', null union all
select 13 ,'sa',' 估价师', 12 
;WITH tree (id, YHM, DepartMC,JB, lvl)
  AS
  ( 
    SELECT id, YHM, DepartMC,JB, 0
    FROM tb
    WHERE id = 3
    UNION all
    SELECT e.id, e.YHM, e.DepartMC,e.JB, es.lvl+1
    FROM tb AS e
      JOIN tree AS es
        ON e.JB = es.id
  )

select * from tree 

--或者

CREATE FUNCTION [dbo].[fn_gettree](@id AS INT) 
    RETURNS @TREE TABLE
(
    id   INT NOT NULL
    ,YHM CHAR(2) NOT NULL
    ,DepartMC nvarchar(120) NULL
    ,JB int 
    ,lvl     INT NOT NULL
)
AS
BEGIN
   ;WITH tree (id, YHM, DepartMC,JB, lvl)
  AS
  ( 
    SELECT id, YHM, DepartMC,JB, 0
    FROM tb
    WHERE id = @id
    UNION all
    SELECT e.id, e.YHM, e.DepartMC,e.JB, es.lvl+1
    FROM tb AS e
      JOIN tree AS es
        ON e.JB = es.id
  )
  INSERT INTO @TREE
    SELECT * FROM tree;

  RETURN
END

select * from dbo.fn_gettree(3)


 --------案例3

 

create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '北京市')
insert into tb values('002' , '001' , '北京市海淀区')
insert into tb values('003' , '001' , '北京市朝阳区')
insert into tb values('004' , '002' , '中关村')
insert into tb values('005' , '003' , '国贸')
insert into tb values('006' , '003' , '朝阳公园')
insert into tb values('007' , '001' , '东城区')
insert into tb values('008' , '007' , '三里屯')
insert into tb values('009' , '002' , '上地')
go
-----函数法

--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go

--调用函数查询北京市)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id

---------CTE方法

DECLARE @ID VARCHAR(3)

--查询ID = '001'的所有子节点
SET @ID = '001'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID