你的位置:首页 > 数据库

[数据库]树形查询扩展


数据库环境:SQL SERVER 2005

  有一层次表的数据如下图1,要实现图2的效果,即将层次结构的路径按行显示,

第一列显示的是叶子节点,后面则是分枝节点,最后是根节点。

图1         图2

  要解决的问题:

  1.递归实现树形查询

      2.从根节点到叶子节点编成一组

  3.动态行转列

  好了,我们依次来解决这些问题。

  0.建表,导入基础数据

WITH x0 AS(SELECT 1 AS id,'1xx' AS name,2 AS fatherID UNION ALLSELECT 2 AS id,'2xx' AS name,3 AS fatherID UNION ALLSELECT 3 AS id,'3xx' AS name,0 AS fatherID UNION ALLSELECT 4 AS id,'4xx' AS name,3 AS fatherID UNION ALLSELECT 0 AS id,'0xx' AS name,NULL AS fatherID)SELECT * INTO #t FROM x0

View Code

  1.递归分组

  分组的组号依据叶子节点的id而定,从叶子节点追溯到根节点,它们属于同一组。

/*递归实现层次查询*/WITH  x0 ( id, name, fatherID, way, level )     AS ( SELECT  id ,            name ,            fatherID ,            CONVERT(VARCHAR(50), CAST(id AS VARCHAR(2))) AS way ,            id AS level        FROM   #t t1        /*从叶子节点开始递归*/        WHERE  NOT EXISTS ( SELECT NULL                   FROM  #t t2                   WHERE t2.fatherID = t1.id )        UNION ALL        SELECT  t1.id ,            t1.name ,            t1.fatherID ,            CONVERT(VARCHAR(50), CAST(t1.id AS VARCHAR(2)) + '->'            + t2.way) AS way ,--路径            t2.level--组别        FROM   #t t1 ,            x0 t2        WHERE  t1.id = t2.fatherID       )

View Code

  层次查询的结果如图:

  2.动态行转列实现

  有了组号,我们则根据同一组号的数据转到一行上展示。这里需要注意行转列的展示顺序,

排在前面的是子节点,后面是父节点。关于动态行转列的实现,可参考我前面的博客 将部分相同的多行记录转成一行多列。

SELECT id ,      name ,      level ,      ROW_NUMBER() OVER ( PARTITION BY level ORDER BY LEN(way) ) AS rn--节点的展示序号  INTO  #t1  FROM  x0  DECLARE @sql NVARCHAR(MAX);  SET @sql = N'';SELECT @sql = @sql + ',max(case rn when ' + CAST(tt.rn AS VARCHAR)    + ' then id end) ' + CASE WHEN rn = 1 THEN +'id'                 ELSE 'faterID'               END + ',max(case rn when '    + CAST(tt.rn AS VARCHAR) + ' then name end) '    + CASE WHEN rn = 1 THEN +'name'        ELSE 'faterName'     ENDFROM  ( SELECT DISTINCT          rn     FROM   #t1    ) ttORDER BY rn;SET @sql = N'select level' + @sql + ' from #t1 group by level';EXEC(@sql);

View Code

(本文完)