收录日期:2020/05/26 05:32:39 时间: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

如何删除正在运行的程序的exe文件? VC和C#有什么不同?还有.net? 求助:方法或方案:怎样用软件实现修改局域网中另一台机子的注册表,要求高效率 请教各位:通用控件(比如ListView)是子窗口,那它有没有自己独立的消息循环吗? 即如何对图片操作,改变它的亮度 *******关于ADO控件和ADO对象****多谢赐教! 如何隐藏MSFlexGrid的某一列? SQl 中某天为星期几或者是第几周的判断? 关于Windows句柄,请问窗口的句柄值和其他句柄(比如hBitmap)是否共享同一个句柄值空间? 再servlet里,怎么将数据库里的数据进行覆盖?? 这是否是vc的bug-----idl文件编译的问题 谁能告诉我要想学习Direct X有什么好书吗? 急!谁能帮我,高分相赠 谁能给我详细讲一讲HTTP隧道技术呀 Delphi中如何实现半透明Panel 怎样架设JSP的Web服务器? 简单的问题,等待你来拿分! 关于窗口的关闭 菜鸟的问题 如何向SQL Server中添加图片字段!(rockynmc) 请帮帮忙 用VC完成了一个俄罗斯方块的游戏,高兴!散分!(我是VC菜鸟!别笑我) 那位朋友,介绍几个不错的音乐网站好吗?谢谢!!! 调查一下,公司给新员工工资有没有蒙的成分? PB程序如何将其自身毁掉或破坏? 菜鸟问个问题 在oracle8.17中,怎样修改用户的最大连接数, 请问谁有VsFlexGrid控件.请发blankrose@sina.com邮箱 程序员工资太低 该成立自己的工会 ▼▼▼▼▼难道没有人能回答么?斑竹是否可把该贴放入精华区才行?!DCOM客户通讯问题!