有一张表tjobmm
fseq ffreight carriercode accountdate fcseq fpseq
111 100 SH001 200908 NULL NULL
112 10 XA01 200908 111 NULL
113 8 MM01 200908 112 NULL
114 11 SH002 200908 NULL 111
115 25 SH001 200908 NULL NULL
116 20 XA01 200907 NULL NULL
117 66 SH001 200908 NULL NULL
118 10 XA02 200908 NULL 117
.
.
.
注:当fcseq和fpseq都是null是,该记录是原单,比如111;当fcseq is not null时是转单,比如112是111的转单,113又是112的转单;当fpseq is not null时是拆单,
比如114是111的拆单;表里肯能存在多级转单或多级拆单;共有几十万条数据,现要按carriercode=SH001和accountdate=200908统计报价的总和,得到如下数据:fseq ffreight ffreightall carriercode accountdate
111 100 129 SH001 200908
115 25 25 SH001 200908
117 66 76 SH001 200908关键是ffreightall的规则:是将原单下面的转单(及下级转单)和拆单(及下级拆单)的ffreight累加起来,如果没有转单和拆单,那么就是原来的ffreight,怎么写查询语句或存储过程能提高效率?
请高手指教
fseq ffreight carriercode accountdate fcseq fpseq
111 100 SH001 200908 NULL NULL
112 10 XA01 200908 111 NULL
113 8 MM01 200908 112 NULL
114 11 SH002 200908 NULL 111
115 25 SH001 200908 NULL NULL
116 20 XA01 200907 NULL NULL
117 66 SH001 200908 NULL NULL
118 10 XA02 200908 NULL 117
.
.
.
注:当fcseq和fpseq都是null是,该记录是原单,比如111;当fcseq is not null时是转单,比如112是111的转单,113又是112的转单;当fpseq is not null时是拆单,
比如114是111的拆单;表里肯能存在多级转单或多级拆单;共有几十万条数据,现要按carriercode=SH001和accountdate=200908统计报价的总和,得到如下数据:fseq ffreight ffreightall carriercode accountdate
111 100 129 SH001 200908
115 25 25 SH001 200908
117 66 76 SH001 200908关键是ffreightall的规则:是将原单下面的转单(及下级转单)和拆单(及下级拆单)的ffreight累加起来,如果没有转单和拆单,那么就是原来的ffreight,怎么写查询语句或存储过程能提高效率?
请高手指教
;with cte as(
select fseq,ffreight ,carriercode ,accountdate ,fcseq ,fpseq
from tjobmm
where fcseq is null and fpseq is null
union all
select c.fseq,t.ffreight ,c.carriercode ,c.accountdate ,c.fcseq ,c.fpseq
from cte c
inner join tjobmm t
on t.fcseq = c.fseq
or t.fpseq = c.fseq
)
select * into #tmp from cteselect fseq ,ffreight, ffreightall = sum(ffreight), carriercode, accountdate
from #tmp
group by fseq,ffreight, carriercode, accountdate