收录日期:2019/10/22 15:09:29 时间:2016/05/28 08:17:35 标签:基础类
假设有 表 a和表b ,b表ID来源于a表,要求统计所有a表中ID的数量
表      a
--------
ID    PID(父ID)
1     0
2     1
3     1
4     1
5     2
6     2
7     5
8     5
--------
表     b
--------
ID    sl (数量)
3     30
4     40
6     60
7     70
8     80
要求输出结果:
-------
ID    sl
1     ?
2     ?
3     ?
4     ?
5     ?
6     ?
7     ?
8     ?

怎么都是??
结构如下:
       1
     / |     2  3  4
   /    5  6
  /  7  8
有子节点的没有数据,数据来源于没有子节点的点(3,4,6,7,8)
要求统计出所有节点的数据:
ID  sl
1   280
2   150
3   30
4   40
5   150
6   60 
7   70
8   80
结构的层次最多有几层?
最多4层
--纯属自娱,没什么适用价值,只是试试看1个语句能不能搞出来。
--推荐用存储过程递归做。
--以下语句只支持最多4层分级,还没试过id3位以上的,2位是没问题的。

--生成测试数据
declare @t1 table(id int,pid int)
declare @t2 table(id int,s1 int)
insert into @t1
select 1,0
union all select 2,1
union all select 3,1
union all select 4,1
union all select 5,2
union all select 6,2
union all select 7,5
union all select 8,5
union all select 12,5
insert into @t2 
select 3,30
union all select 4,40
union all select 6,60
union all select 7,70
union all select 8,80
union all select 12,20

SELECT id = cast(RIGHT(c, charindex('.', reverse(c)) - 1) AS int), s1 =
          (SELECT SUM(s1)
         FROM (SELECT c, s1
                 FROM (SELECT c = isnull
                                   ((SELECT c
                                   FROM (SELECT c = isnull
                                                     ((SELECT c
                                                     FROM (SELECT c = cast(pid AS varchar(2)) 
                                                                   + '.' + cast(id AS varchar(2))
                                                             FROM @t1) t13
                                                     WHERE LEFT(t14.c, charindex('.', t14.c) 
                                                           - 1) = RIGHT(c, charindex('.', 
                                                           reverse(c)) - 1)), 0) + RIGHT(c, len(c) 
                                                 - charindex('.', t14.c) + 1)
                                           FROM (SELECT c = cast(pid AS varchar(2)) 
                                                         + '.' + cast(id AS varchar(2))
                                                   FROM @t1) t14) t11
                                   WHERE LEFT(t7.c, charindex('.', t7.c) - 1) = RIGHT(c, 
                                         charindex('.', reverse(c)) - 1)), 0) + RIGHT(c, len(c) 
                               - charindex('.', t7.c) + 1)
                         FROM (SELECT c = isnull
                                           ((SELECT c
                                           FROM (SELECT c = cast(pid AS varchar(2)) 
                                                         + '.' + cast(id AS varchar(2))
                                                   FROM @t1) t15
                                           WHERE LEFT(t9.c, charindex('.', t9.c) - 1) 
                                                 = RIGHT(c, charindex('.', reverse(c)) - 1)), 0) 
                                       + RIGHT(c, len(c) - charindex('.', t9.c) + 1)
                                 FROM (SELECT c = cast(pid AS varchar(2)) 
                                               + '.' + cast(id AS varchar(2))
                                         FROM @t1) t9) t7) t1 LEFT OUTER JOIN
                       @t2 t2 ON cast(RIGHT(t1.c, charindex('.', reverse(t1.c)) - 1) AS int) 
                       = t2.id) t3
         WHERE t3.c LIKE t4.c + '.%' OR
               t3.c = t4.c)
FROM (SELECT c
        FROM (SELECT c = isnull
                          ((SELECT c
                          FROM (SELECT c = isnull
                                            ((SELECT c
                                            FROM (SELECT c = cast(pid AS varchar(2)) 
                                                          + '.' + cast(id AS varchar(2))
                                                    FROM @t1) t16
                                            WHERE LEFT(t17.c, charindex('.', t17.c) - 1) 
                                                  = RIGHT(c, charindex('.', reverse(c)) - 1)), 0) 
                                        + RIGHT(c, len(c) - charindex('.', t17.c) + 1)
                                  FROM (SELECT c = cast(pid AS varchar(2)) 
                                                + '.' + cast(id AS varchar(2))
                                          FROM @t1) t17) t12
                          WHERE LEFT(t8.c, charindex('.', t8.c) - 1) = RIGHT(c, charindex('.', 
                                reverse(c)) - 1)), 0) + RIGHT(c, len(c) - charindex('.', t8.c) + 1)
                FROM (SELECT c = isnull
                                  ((SELECT c
                                  FROM (SELECT c = cast(pid AS varchar(2)) 
                                                + '.' + cast(id AS varchar(2))
                                          FROM @t1) t18
                                  WHERE LEFT(t10.c, charindex('.', t10.c) - 1) = RIGHT(c, 
                                        charindex('.', reverse(c)) - 1)), 0) + RIGHT(c, len(c) 
                              - charindex('.', t10.c) + 1)
                        FROM (SELECT c = cast(pid AS varchar(2)) 
                                      + '.' + cast(id AS varchar(2))
                                FROM @t1) t10) t8) t5 LEFT OUTER JOIN
              @t2 t6 ON cast(RIGHT(t5.c, charindex('.', reverse(t5.c)) - 1) AS int) = t6.id) t4

--返回结果
(所影响的行数为 9 行)


(所影响的行数为 6 行)

id          s1          
----------- ----------- 
1           300
2           230
3           30
4           40
5           170
6           60
7           70
8           80
12          20

(所影响的行数为 9 行)
说不适用,主要指维护上太麻烦,体力活。
--无限制的计算存储过程
create proc p_calc
as
set nocount on
select a.id,a.pid,sl=isnull(b.sl,0),total=-1
into #t 
from ta a left join(
select id,sl=sum(sl) from tb
group by id
)b on a.id=b.id

--分级计算
update #t set total=sl
from #t a
where not exists(
select 1 from #t where pid=a.id)
while @@rowcount>0
update #t set total=isnull(a.sl,0)+isnull(b.total,0)
from #t a join(
select pid,total=sum(total) from #t
where total<>0 group by pid
)b on a.id=b.pid
where a.total=-1 and not exists(
select 1 from #t where pid=a.id and total=-1)
select id,sl=total from #t
go

--调用存储过程进行计算
exec p_calc
--测试

--测试数据
create table ta(ID int,PID int)
insert ta select 1,0
union all select 2,1
union all select 3,1
union all select 4,1
union all select 5,2
union all select 6,2
union all select 7,5
union all select 8,5

create table tb(ID int,sl int)
insert tb select 3,30
union all select 4,40
union all select 6,60
union all select 7,70
union all select 8,80
go

--计算的存储过程
create proc p_calc
as
set nocount on
select a.id,a.pid,sl=isnull(b.sl,0),total=-1
into #t 
from ta a left join(
select id,sl=sum(sl) from tb
group by id
)b on a.id=b.id

--分级计算
update #t set total=sl
from #t a
where not exists(
select 1 from #t where pid=a.id)
while @@rowcount>0
update #t set total=isnull(a.sl,0)+isnull(b.total,0)
from #t a join(
select pid,total=sum(total) from #t
where total<>0 group by pid
)b on a.id=b.pid
where a.total=-1 and not exists(
select 1 from #t where pid=a.id and total=-1)
select id,sl=total from #t
go

--调用存储过程进行计算
exec p_calc
go

--删除测试
drop table ta,tb
drop proc p_calc

/*--测试结果

id          sl          
----------- ----------- 
1           280
2           210
3           30
4           40
5           150
6           60
7           70
8           80
--*/
适合无限级别的函数:

create table t1(id int,pid int)
create table t2(id int,s1 int)
insert into t1
select 1,0
union all select 2,1
union all select 3,1
union all select 4,1
union all select 5,2
union all select 6,2
union all select 7,5
union all select 8,5
union all select 12,5
insert into t2 
select 3,30
union all select 4,40
union all select 6,60
union all select 7,70
union all select 8,80
union all select 12,20

select * from t1
select * from t2

alter function fn_test(@a int)
--北风
--2004.7.8
returns int
as
begin
   declare @b int
   declare @t table(a int)
   insert @t select id from t1 where pid=@a
   --if(@@rowcount>0)
   --begin
   while @@rowcount>0
     insert @t 
select id from t1 where pid in(select a from @t) 
and 
id not in(select a from @t)

   select @b=sum(s1) from t2 where id in(select a from @t)
   
   --当为NULL时,表示 @a 不在 pid 中
   if(@b is null)
     select @b=sum(s1) from t2 where id=@a
   return @b
end

--调用
select id,dbo.fn_test(id) s1 from t1


id          s1          
----------- ----------- 
1           300
2           230
3           30
4           40
5           170
6           60
7           70
8           80
12          20

(所影响的行数为 9 行)
alter function fn_test
改为
create function fn_test

帮忙看下为什么会进入catch(ConnectionNotFoundException cnfe) 里面, 一道关于get函数的初等问题 面试时问到的极其常见且重要的问题 谁有BCGControl Pro V10.0的注册序列号? 或者破解版啊? OpenCV的高手进来! 出现错误了!! 如何在另一进程的全屏游戏的表面(directX)上写字 一个字所表示的数值范围的问题 老婆给生个胖小子,名还没取呢,高兴,散分 关于vb2005 string 长度限定问题 CString转换为int的一个问题,求高手 java中静态方法调用非静态变量的问题 access数据库的问题,求助~~~~~~~~~~~~~~~~~~~~~~~ 一个关于数组输出的面试题,急 jsp中插入swf的奇怪问题!!! 怎样用C++新建(删除)一个文件或文件夹 重复30次插入已有的数据 进程虚拟和系统虚拟的概念有什么不一样呢 怎样读取txt中数字,字符串,汉字部分 JS与TXT文本的问题。。请高手进,在线等+++++++++++ J2EE自动生成工具在线演示系统源码发布 Socket问题 文件读写疑问 给推荐一款免费的好用的网络加速器 帮忙看下为什么会进入catch(ConnectionNotFoundException cnfe) 里面, J2EE自动生成工具在线演示系统源码发布 获取用USB to IDE 连接的存储设备的设备信息(厂商、序列号什么的) td{width:100%} .td{width:100%} #td{width:100%} 区别是什么。 有的人个样就系老态,比亲哥少了十岁,不过个样比他老佐十岁。。 datagridview 单元格动态输入问题请教!