双编码数的统计

Question

--测试数据   深度排序     
  DECLARE   @t   TABLE(ID   char(3),PID   char(3),Name   nvarchar(10))   
  INSERT   @t   SELECT   '001',NULL   ,'山东省'   
  UNION   ALL   SELECT   '002','001','烟台市'   
  UNION   ALL   SELECT   '004','002','招远市'   
  UNION   ALL   SELECT   '003','001','青岛市'   
  UNION   ALL   SELECT   '005',NULL   ,'四会市'   
  UNION   ALL   SELECT   '006','005','清远市'   
  UNION   ALL   SELECT   '007','006','小分市'   
    
  --深度排序显示处理   
  --生成每个节点的编码累计(相同当单编号法的编码)   
  DECLARE   @t_Level   TABLE(ID   char(3),Level   int,Sort   varchar(8000))   
  DECLARE   @Level   int   
  SET   @Level=0   
  INSERT   @t_Level   SELECT   ID,@Level,ID   
  FROM   @t   
  WHERE   PID   IS   NULL   
  WHILE   @@ROWCOUNT>0   
  BEGIN   
  SET   @Level=@Level+1   
  INSERT   @t_Level   SELECT   a.ID,@Level,b.Sort+a.ID   
  FROM   @t   a,@t_Level   b   
  WHERE   a.PID=b.ID   
  AND   b.Level=@Level-1   
  END   
    
  --显示结果   
  SELECT   a.*   
  FROM   @t   a,@t_Level   b   
  WHERE   a.ID=b.ID   
  ORDER   BY   b.Sort   
  /*--结果   
  ID       PID       Name                 
  ------   ---------   ----------     
  001     NULL   山东省   
  002     001       烟台市   
  004     002       招远市   
  003     001       青岛市   
  005     NULL   四会市   
  006     005       清远市   
  007     006       小分市   
  --*/--查询指定节点及其所有子节点的函数   
  CREATE   FUNCTION   f_Cid(@ID   char(3))   
  RETURNS   @t_Level   TABLE(ID   char(3),Level   int)   
  AS   
  BEGIN   
  DECLARE   @Level   int   
  SET   @Level=1   
  INSERT   @t_Level   SELECT   @ID,@Level   
  WHILE   @@ROWCOUNT>0   
  BEGIN   
  SET   @Level=@Level+1   
  INSERT   @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   
    
  --调用函数查询002及其所有子节点   
  SELECT   a.*   
  FROM   tb   a,f_Cid('002')   b   
  WHERE   a.ID=b.ID   
  /*--结果   
  ID       PID     Name                 
  ------   -------   ----------     
  002     001     烟台市   
  004     002     招远市   
  --*/ --测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
    SET @Level=@Level+1
    INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
    FROM @t a,@t_Level b
    WHERE a.PID=b.ID
        AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/

Answer 1

-->生成测试数据
DECLARE @Tab TABLE(
id  VARCHAR(10),  pid VARCHAR(10))
INSERT INTO @Tab 
SELECT '01',    NULL UNION ALL 
SELECT '02',    '01' UNION ALL 
SELECT '03',    '02' UNION ALL 
SELECT '04',    null UNION ALL 
SELECT '05',    '04' UNION ALL 
SELECT '06',    '05' UNION ALL 
SELECT '07',    '06' UNION ALL 
SELECT '08',    null UNION ALL 
SELECT '09',    '02' UNION ALL 
SELECT '10',   '09'-->生成测试
;WITH Args AS 
(
SELECT * FROM @Tab WHERE id ='02'
UNION ALL 
SELECT a.id,a.pid FROM @Tab a,Args b WHERE a.pid= b.id
)SELECT A.id,SUM(CASE WHEN b.id IS NOT NULL THEN 1 ELSE 0 end) cnt FROM (
(SELECT * FROM Args WHERE pid = '02') A  left JOIN (SELECT * FROM Args) B ON a.id = b.pid )
GROUP BY A.id-->生成结果
id         cnt
---------- -----------
03         0
09         1(2 行受影响)

Answer 2

DECLARE @t TABLE(id  VARCHAR(10),  pid VARCHAR(10))
INSERT @t SELECT '01',    null
UNION ALL SELECT '02' ,   '01'
UNION ALL SELECT '03'  ,  '02'
UNION ALL SELECT '04'   , null
UNION ALL SELECT '05'    ,'04'
UNION ALL SELECT '06',    '05'
UNION ALL SELECT '07' ,   '06'
UNION ALL SELECT '08'  ,  null
UNION ALL SELECT '09'   , '02'
UNION ALL SELECT '10'    ,'09' 
;
WITH fc AS
(
SELECT id,pid,pt=CAST(ID AS VARCHAR(1000)) FROM @t WHERE pid IS NULL
UNION ALL
SELECT b.id,b.pid,pt=CAST(a.pt + ',' + b.id AS VARCHAR(1000))
FROM fc a
INNER JOIN @t b
ON a.id = b.pid
)
SELECT * FROM fc a
CROSS APPLY
(
SELECT COUNT(*) cnt FROM fc WHERE pt LIKE a.pt + '_%'
) b
--WHERE a.pid ='02'  /*结果全出来了，要哪个在这里加条件就是了*/

Answer 3

declare @tb table(id varchar(100),level int,sort varchar(100),pid varchar(100))
declare @level int
set @level=1
insert into @tb 
select id,@level,','+cast(id as varchar),pid from tb where pid is nullwhile @@rowcount>0
begin
  set @level=@level+1
  insert into @tb
  select tb.id ,@level,sort+','+cast(tb.id as varchar),tb.pid
  from tb,@tb t
  where tb.pid=t.id and t.level=@level-1
end
select * from tb  join(SELECT tb.id,COUNT(*)-1 cnt FROM @tb ,tb WHERE sort LIKE '%'+tb.id + '%' group by tb.id) k
    on tb.id=k.id
   where tb.pid='02'

Answer 4

实现： 
双编码数的统计 tb
id    pid 
01    null 
02    01 
03    02 
04    null 
05    04 
06    05 
07    06 
08    null 
09    02 
10    09 比如，我传pid=02 
则统计处pid=02的id的所有子目录数 
id 
03    0 
09    1 如 pid = null 
01    4 
04    3 
08    0 现在，还有一个问题
就是
比如：
我还有一张表  tab (id,tid) ---------> tid 是tb的主键
我想也在上面统计的基础上统计出比如：03  下有多少  id (tab)其实这实际需求就是：资源目录的统计（目录统计和目录下面的文档统计，如果某目录下没有则0）
不知道我说清楚了没
大家看能怎么实现？谢谢，我再追加分

Answer 5

03  下有多少  id (tab) 和上面的有什么区别？

Answer 6

没什么，但是有关联，想在一个地方一起统计出结果
比如
id  子目录  文档数
03   0     0
09   1     0如果03下有文档，则统计出所有的文档数，包括子目录下的文档
通过tb的id与tab的tid关联的

Answer 7

select * ，文档数 =(select count(*) from tab where id=tb.id)
   from tb  join(SELECT tb.id,COUNT(*)-1 cnt FROM @tb ,tb WHERE sort LIKE '%'+tb.id + '%' group by tb.id) k 
    on tb.id=k.id 
  where tb.pid='02' 这样?

Answer 8

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(2),[pid] varchar(2))
insert [tb]
select '01',null union all
select '02','01' union all
select '03','02' union all
select '04',null union all
select '05','04' union all
select '06','05' union all
select '07','06' union all
select '08',null union all
select '09','02' union all
select '10','09'
go
--select * from [tb]declare @pid varchar(10)
set @pid='02'
--set @pid=null
;with szx as
(
select rootid=id,id,pid from tb where isnull(pid,'')=isnull(@pid,'')
union all
select a.rootid,b.id,b.pid
from szx a join tb b
on a.id=b.pid
)
select rootid as id,count(1)-1 as '子目录数' from szx group by rootid-- @pid='02'
/*
id   子目录数
---- -----------
03   0
09   1(2 行受影响)
*/
-- @pid=null
/*
id   子目录数
---- -----------
01   4
04   3
08   0(3 行受影响)
*/

Answer 9

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(2),[pid] varchar(2))
insert [tb]
select '01',null union all
select '02','01' union all
select '03','02' union all
select '04',null union all
select '05','04' union all
select '06','05' union all
select '07','06' union all
select '08',null union all
select '09','02' union all
select '10','09'
if object_id('[tab]') is not null drop table [tab]
go
create table [tab]([id] int,[tid] varchar(2))
insert [tab]
select 1,'03' union all
select 2,'03' union all
select 3,'03' union all
select 4,'09'
go
select * from [tb]
select * from [tab]declare @pid varchar(10)
set @pid='02'
--set @pid=null
;with szx as
(
select rootid=id,id,pid from tb where isnull(pid,'')=isnull(@pid,'')
union all
select a.rootid,b.id,b.pid
from szx a join tb b
on a.id=b.pid
)
select a.id,a.子目录数,b.文档数
from 
(
select rootid as id,count(1)-1 as '子目录数' 
from szx 
group by rootid
) a
join
(
select rootid as id,count(b.id) as '文档数' 
from szx a left join tab b
on a.id=b.tid
group by rootid
) b
on a.id=b.id-- @pid='02'
/*
id   子目录数        文档数
---- ----------- -----------
03   0           3
09   1           1(2 行受影响)
*/
-- @pid=null
/*
id   子目录数        文档数
---- ----------- -----------
01   4           4
04   3           0
08   0           0(3 行受影响)
*/