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

古城微笑少年丶 2022-06-16 11:26 245阅读 0赞

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