建立一个树形数据库中的表pjtItem
ParentID,ItemID,Item
A01 ddd
A01 A0101 ccc
A0101 A010101 aaa1
A0101 A010102 aaa2 最上级结点设置为空,ItemID任意设置,不一定按照我举例的格式生成。现在想做一个查询:
有出库表(GetOutMST,GetOutDtl主从关系,关联字段DH,主表与pjtItem关联字段ItemID),
还有张工程完成表(pjtCheckMST,PjtCheckDtl主从关系,关联字段DH,主表与pjtItem关联字段ItemID),
想把他们的汇总金额汇集到总结点。格式如下:
ItemID Item GTotal PTotal如:
领料金额 完成金额
A0101 A010101 aaa1 1,000 15,000
A0101 A010102 aaa2 590 10,000生成的查询结果应为:
ItemID Item GTotal PTotal
A01 ddd 1,590 25,000
也就是将所有金额汇总到其父结点为空的上级结点
ParentID,ItemID,Item
A01 ddd
A01 A0101 ccc
A0101 A010101 aaa1
A0101 A010102 aaa2 最上级结点设置为空,ItemID任意设置,不一定按照我举例的格式生成。现在想做一个查询:
有出库表(GetOutMST,GetOutDtl主从关系,关联字段DH,主表与pjtItem关联字段ItemID),
还有张工程完成表(pjtCheckMST,PjtCheckDtl主从关系,关联字段DH,主表与pjtItem关联字段ItemID),
想把他们的汇总金额汇集到总结点。格式如下:
ItemID Item GTotal PTotal如:
领料金额 完成金额
A0101 A010101 aaa1 1,000 15,000
A0101 A010102 aaa2 590 10,000生成的查询结果应为:
ItemID Item GTotal PTotal
A01 ddd 1,590 25,000
也就是将所有金额汇总到其父结点为空的上级结点
declare @pjtItem table([ParentID] nvarchar(5),[ItemID] nvarchar(7),[Item] nvarchar(4))
Insert @pjtItem
select null,N'A01',N'ddd' union all
select N'A01',N'A0101',N'ccc' union all
select N'A0101',N'A010101',N'aaa1' union all
select N'A0101',N'A010102',N'aaa2'
--Select * from @pjtItem
declare @B table([ParentID] nvarchar(5),[ItemID] nvarchar(7),[Item] nvarchar(4),[GTotal] INT,[PTotal] INT)
Insert @B
select N'A0101',N'A010101',N'aaa1',N'1000',N'15000' union all
select N'A0101',N'A010102',N'aaa2',N'590',N'10000'
Select A.ITEMID,A.ITEM,SUM([GTotal]) AS [GTotal],SUM([PTotal]) AS [PTotal] from @pjtItem A
left join @B B on B.[ItemID] LIKE A.[ItemID]+'%'
WHERE A.[ParentID] IS NULL
GROUP BY A.ITEMID,A.ITEM
/*
ITEMID ITEM GTotal PTotal
------- ---- ----------- -----------
A01 ddd 1590 25000
*/
ParentID ItemID Item
A01 ddd
A01 B01 ccc
B01 C01 bbb
B01 d2 aaa
如果是这样的话,怎么统计的出来?
这只是举一个结点,还有其它很多结点呢!
或者在2005里面使用CTE
create table tb(parentid varchar(20),itemid varchar(20),item nvarchar(20),gTotal int,PTotal int)
insert into tb select 'A0101','A010101','aaa1',1000,15000
insert into tb select 'A0101','A010102','aaa2',590,10000
insert into tb select null,'A01','ddd',0,0
insert into tb select 'A01','A0101','ccc',0,0
insert into tb select null,'A02','eee',0,0
insert into tb select 'A02','A0201','fff',0,0
insert into tb select 'A0201','A020101','kkk',1200,5000
insert into tb select 'A0201','A020102','xxx',256,4855
go
declare @searchitemid varchar(20);
set @searchitemid='A01';
with temptab(parentid,itemid,item,gtotal,ptotal)as
(select root.parentid,root.itemid,root.item,root.gtotal,root.ptotal
from tb root
where root.itemid=@searchitemid
union all
select sub.parentid,sub.itemid,sub.item,sub.gtotal,sub.ptotal
from tb sub inner join temptab super
on sub.parentid=super.itemid
-- where
)
select itemid,item,
(select sum(gtotal) from temptab)as gtotal,
(select sum(Ptotal) from temptab)as Ptotal
from tb where itemid=@searchitemid
go
drop table tb
/*
itemid item gtotal Ptotal
-------------------- -------------------- ----------- -----------
A01 ddd 1590 25000(1 行受影响)
*/