首页 新闻 会员 周边 捐助

关于Sql Server 分组统计

0
悬赏园豆：100 [已解决问题] 解决于 2014-08-26 12:40 
with [主表] (ID, 包号, 上级包号, 打包数量) as(
    select 1,'20ST01-Y-ST-0001','20ST01-Z-ST-0001','0' union all
    select 2,'20ST01-X-ST-0001','20ST01-Y-ST-0001','0' union all
    select 3,'20ST01-H-ST-0001','20ST01-X-ST-0001','2' union all
    select 4,'20ST01-H-ST-0002','20ST01-X-ST-0001','2' union all
    select 5,'20ST01-H-ST-0003','20ST01-X-ST-0001','1' union all
    select 6,'20ST01-X-ST-0002','20ST01-Y-ST-0001','0' union all
    select 7,'20ST01-H-ST-0004','20ST01-X-ST-0002','1' union all
    select 8,'20ST01-H-ST-0005','20ST01-X-ST-0002','1' union all
    select 9,'20ST01-H-ST-0006','20ST01-X-ST-0002','3'
)
,[统计表] AS(
    SELECT ID,包号,上级包号,打包数量,cast(包号 as nvarchar(max)) as TE,cast(包号 as nvarchar(max)) as TreePath,
    0 as TreeLevel,ROW_NUMBER()over(order by getdate()) as OrderID
    FROM [主表]
    UNION ALL
    SELECT PM.ID,PM.包号,PM.上级包号,PM.打包数量,cast(replicate(' ',(CTE.TreeLevel+1)*4)+'|_'+PM.包号 as nvarchar(MAX)) as TE,
    cast(CTE.TreePath+'::'+PM.包号 as nvarchar(MAX)) as TreePath,CTE.TreeLevel+1 as TreeLevel,
    CTE.OrderID*100+ROW_NUMBER()over(Order by GETDATE()) as OrderID
    FROM [主表] PM
    INNER JOIN [统计表] CTE ON PM.上级包号=CTE.包号
)
SELECT * FROM [统计表] WHERE TreePath LIKE '20ST01-Y-ST-0001%'
ORDER BY LTRIM(OrderID);

该语句执行结果：

1    20ST01-Y-ST-0001    20ST01-Z-ST-0001    0    20ST01-Y-ST-0001    20ST01-Y-ST-0001    0    1
2    20ST01-X-ST-0001    20ST01-Y-ST-0001    0        |_20ST01-X-ST-0001    20ST01-Y-ST-0001::20ST01-X-ST-0001    1    101
3    20ST01-H-ST-0001    20ST01-X-ST-0001    2            |_20ST01-H-ST-0001    20ST01-Y-ST-0001::20ST01-X-ST-0001::20ST01-H-ST-0001    2    10101
4    20ST01-H-ST-0002    20ST01-X-ST-0001    2            |_20ST01-H-ST-0002    20ST01-Y-ST-0001::20ST01-X-ST-0001::20ST01-H-ST-0002    2    10102
5    20ST01-H-ST-0003    20ST01-X-ST-0001    1            |_20ST01-H-ST-0003    20ST01-Y-ST-0001::20ST01-X-ST-0001::20ST01-H-ST-0003    2    10103
6    20ST01-X-ST-0002    20ST01-Y-ST-0001    0        |_20ST01-X-ST-0002    20ST01-Y-ST-0001::20ST01-X-ST-0002    1    102
7    20ST01-H-ST-0004    20ST01-X-ST-0002    1            |_20ST01-H-ST-0004    20ST01-Y-ST-0001::20ST01-X-ST-0002::20ST01-H-ST-0004    2    10201
8    20ST01-H-ST-0005    20ST01-X-ST-0002    1            |_20ST01-H-ST-0005    20ST01-Y-ST-0001::20ST01-X-ST-0002::20ST01-H-ST-0005    2    10202
9    20ST01-H-ST-0006    20ST01-X-ST-0002    3            |_20ST01-H-ST-0006    20ST01-Y-ST-0001::20ST01-X-ST-0002::20ST01-H-ST-0006    2    10203

希望得到结果：

1    20ST01-Y-ST-0001    20ST01-Z-ST-0001    10    20ST01-Y-ST-0001    20ST01-Y-ST-0001    0    1
2    20ST01-X-ST-0001    20ST01-Y-ST-0001    5        |_20ST01-X-ST-0001    20ST01-Y-ST-0001::20ST01-X-ST-0001    1    101
3    20ST01-H-ST-0001    20ST01-X-ST-0001    2            |_20ST01-H-ST-0001    20ST01-Y-ST-0001::20ST01-X-ST-0001::20ST01-H-ST-0001    2    10101
4    20ST01-H-ST-0002    20ST01-X-ST-0001    2            |_20ST01-H-ST-0002    20ST01-Y-ST-0001::20ST01-X-ST-0001::20ST01-H-ST-0002    2    10102
5    20ST01-H-ST-0003    20ST01-X-ST-0001    1            |_20ST01-H-ST-0003    20ST01-Y-ST-0001::20ST01-X-ST-0001::20ST01-H-ST-0003    2    10103
6    20ST01-X-ST-0002    20ST01-Y-ST-0001    5        |_20ST01-X-ST-0002    20ST01-Y-ST-0001::20ST01-X-ST-0002    1    102
7    20ST01-H-ST-0004    20ST01-X-ST-0002    1            |_20ST01-H-ST-0004    20ST01-Y-ST-0001::20ST01-X-ST-0002::20ST01-H-ST-0004    2    10201
8    20ST01-H-ST-0005    20ST01-X-ST-0002    1            |_20ST01-H-ST-0005    20ST01-Y-ST-0001::20ST01-X-ST-0002::20ST01-H-ST-0005    2    10202
9    20ST01-H-ST-0006    20ST01-X-ST-0002    3            |_20ST01-H-ST-0006    20ST01-Y-ST-0001::20ST01-X-ST-0002::20ST01-H-ST-0006    2    10203

目的就是统计下级节点的PkgQty给上级节点，希望诸位大神能指点一二

Label的主页 Label | 初学一级 | 园豆：112
提问于：2014-08-25 16:21
< >
分享
最佳答案
0 
with [主表] (ID, 包號, 上級包號, 打包數量) as(
    select 1,'20ST01-Y-ST-0001','20ST01-Z-ST-0001',0 union all
    select 2,'20ST01-X-ST-0001','20ST01-Y-ST-0001',0 union all
    select 3,'20ST01-H-ST-0001','20ST01-X-ST-0001',2 union all
    select 4,'20ST01-H-ST-0002','20ST01-X-ST-0001',2 union all
    select 5,'20ST01-H-ST-0003','20ST01-X-ST-0001',1 union all
    select 6,'20ST01-X-ST-0002','20ST01-Y-ST-0001',0 union all
    select 7,'20ST01-H-ST-0004','20ST01-X-ST-0002',1 union all
    select 8,'20ST01-H-ST-0005','20ST01-X-ST-0002',1 union all
    select 9,'20ST01-H-ST-0006','20ST01-X-ST-0002',3
)
,[統計表] AS(
    SELECT ID,包號,上級包號,打包數量,cast(包號 as nvarchar(max)) as TE,cast(包號 as nvarchar(max)) as TreePath,
    0 as TreeLevel,ROW_NUMBER()over(order by getdate()) as OrderID
    FROM [主表]
    UNION ALL
    SELECT PM.ID,PM.包號,PM.上級包號,PM.打包數量,cast(replicate(' ',(CTE.TreeLevel+1)*4)+'|_'+PM.包號 as nvarchar(MAX)) as TE,
    cast(CTE.TreePath+'::'+PM.包號 as nvarchar(MAX)) as TreePath,CTE.TreeLevel+1 as TreeLevel,
    CTE.OrderID*100+ROW_NUMBER()over(Order by GETDATE()) as OrderID
    FROM [主表] PM
    INNER JOIN [統計表] CTE ON PM.上級包號=CTE.包號
)
--SELECT * FROM [統計表] WHERE TreePath LIKE '20ST01-Y-ST-0001%'
--ORDER BY LTRIM(OrderID);

SELECT * into #t FROM [統計表] WHERE TreePath LIKE '20ST01-Y-ST-0001%'
ORDER BY LTRIM(OrderID);
declare @level int
select @level= MAX(TreeLevel) from #t;
while @level>0
begin
update #t set 打包數量=aa.打包數量 from (select 上級包號,SUM(打包數量)as 打包數量 from #t where TreeLevel=@level group by 上級包號) as aa 
where #t.TreeLevel=@level-1 and #t.包號=aa.上級包號 
set @level=@level-1
end
select * from #t
drop table #t

上面的sql應該實現你的要求，但是有部分改動，請將sql中的字段名改為簡體，將打包數量的數據類型改為int，也使用了臨時表.

收获园豆：100
会飞的金鱼 | 小虾三级 |园豆：881 | 2014-08-26 11:24
其他回答(4)
0

眼睛都看花了…建议先做几个视图，然后对视图进行统计。

Firen | 园豆：5385 (大侠五级) | 2014-08-25 16:29
0

额，确实很眼花，能配点文字说明一下问题吗？

晓菜鸟 | 园豆：2594 (老鸟四级) | 2014-08-25 16:57

 

大概要实现的效果就是：

比如说包号：20ST01-Y-ST-0001的打包数量，就是TreePath以20ST01-Y-ST-0001开头的打包数量和，即为10，

包号：20ST01-X-ST-0001的打包数量，就是TreePath以20ST01-Y-ST-0001::20ST01-X-ST-0001开头的打包数量和，即为2+2+1=5

支持(0) 反对(0) Label | 园豆：112 (初学一级) | 2014-08-25 17:05
0

写个函数统计下级数量

ジ☆véjeff | 园豆：202 (菜鸟二级) | 2014-08-25 17:52
0

建议写个函数或者存储过程，将查询的返回结果，在打包数量那列进行数据绑定。

伏草惟存 | 园豆：1420 (小虾三级) | 2014-08-25 17:58
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。