替代料查询求解

Question

表
T_A T_B
ITEM_CODE QTY ITEM_CODE  ITEM_CODE_D
a 200 a e
b -100 b e
c 55
d 88
想得到的结果 表 T_C
ITEM_CODE QTY SUM_QTY
a 200 100
b -100 0
c 55 55
d 88 88T_B的两列的意思是
ITEM_CODE 跟 ITEM_CODE_D 互为替代，
如果a,e互为替代，b,e互为替代，那么a,b,e就互为替代。
SUM_QTY 这列的逻辑是 如果  T_C.ITEM_CODE 在T_B中存在，则这一组替代料中的第一行sum_Qty = sum(这组的数量)，这组的其他行sum_qty为0，如果 T_C.ITEM_CODE 在T_B中不存在，则sum_qty = qty.想了好久想不出来，求助高手。

Answer 1

则这一组替代料中的第一行sum_Qty = sum(这组的数量)请问你的100和0是怎么计算来的?你把公式或数据说明一下.比如100 = .. + .. - ...

Answer 2

第一行 100 = sum(200+(-100)),0(这组的其他行的sum_Qty 为0)

Answer 3

create table ta(ITEM_CODE varchar(10),QTY int)
create table tb(ITEM_CODE varchar(10),ITEM_CODE_D varchar(10))
insert ta select 'a',200
union all select 'b',-100
union all select 'f',100
union all select 'c',55
union all select 'd',88insert tb select 'a','e'
union all select 'b','e'
union all select 'f','e';with test1 as
(
select id=row_number() over(order by (select 1)),
grp=dense_rank() over(order by isnull(b.ITEM_CODE_D,a.ITEM_CODE)),
a.ITEM_CODE,
a.QTY,ITEM_CODE_D=isnull(b.ITEM_CODE_D,a.ITEM_CODE)
from ta a left join tb b
on a.ITEM_CODE=b.ITEM_CODE
)
,test2 as
(
select id,QTY,ITEM_CODE,SUM_QTY=(select sum(QTY) from test1 where ITEM_CODE_D=t1.ITEM_CODE_D and id>=t1.id),ITEM_CODE_D from test1 t1
)
,test3 as
(
select * from test2 t where  id=(select top 1 id from test2 where ITEM_CODE_D=t.ITEM_CODE_D order by id)
)
select ITEM_CODE=isnull(t1.ITEM_CODE,t2.ITEM_CODE),QTY=isnull(t1.QTY,t2.QTY),SUM_QTY=isnull(SUM_QTY,0) from test3 t1 
right join ta t2 on t1.ITEM_CODE=t2.ITEM_CODEdrop table ta,tbITEM_CODE  QTY         SUM_QTY
---------- ----------- -----------
a          200         200
b          -100        0
f          100         0
c          55          55
d          88          88

Answer 4

create table ta(ITEM_CODE varchar(10),QTY int)
create table tb(ITEM_CODE varchar(10),ITEM_CODE_D varchar(10))
insert ta select 'a',200
union all select 'b',-100
union all select 'f',100
union all select 'c',55
union all select 'd',88insert tb select 'a','e'
union all select 'b','e'
union all select 'f','e';with test1 as
(
select id=row_number() over(order by (select 1)),
a.ITEM_CODE,
a.QTY,ITEM_CODE_D=isnull(b.ITEM_CODE_D,a.ITEM_CODE)
from ta a left join tb b
on a.ITEM_CODE=b.ITEM_CODE
)
,test2 as
(
select id,QTY,ITEM_CODE,SUM_QTY=(select sum(QTY) from test1 where ITEM_CODE_D=t1.ITEM_CODE_D and id>=t1.id),ITEM_CODE_D from test1 t1
)
,test3 as
(
select * from test2 t where  id=(select top 1 id from test2 where ITEM_CODE_D=t.ITEM_CODE_D order by id)
)
select ITEM_CODE=isnull(t1.ITEM_CODE,t2.ITEM_CODE),QTY=isnull(t1.QTY,t2.QTY),SUM_QTY=isnull(SUM_QTY,0) from test3 t1 
right join ta t2 on t1.ITEM_CODE=t2.ITEM_CODEdrop table ta,tbITEM_CODE  QTY         SUM_QTY
---------- ----------- -----------
a          200         200
b          -100        0
f          100         0
c          55          55
d          88          88(5 row(s) affected)

Answer 5

Your result doesn't look quite right. a, b, e and f should belong to the same group according to OP's requirement
I would use sets to implement what he requires:
drop table t_a
drop table t_b
drop table #substitute_sets
drop table #substitute_components
gocreate table t_a(ITEM_CODE varchar(10),QTY int)
create table t_b(ITEM_CODE varchar(10),ITEM_CODE_D varchar(10))
insert t_a 
select 'a',200
union all select 'b',-100
union all select 'f',100
union all select 'c',55
union all select 'd',88insert t_b 
select 'a','e'
union all select 'b','e'
union all select 'f','e'
gocreate table #substitute_sets
(
set_id int identity(1,1) primary key,
set_name varchar(max)
)create table #substitute_components
(
component_id int identity(1,1) primary key,
component_code varchar(10),
set_id int
)
godeclare @item_code varchar(10), @item_code_d varchar(10), @set_id intdeclare substitute_cursor cursor local for
select
item_code, item_code_d
from
t_bopen substitute_cursorfetch next from substitute_cursor into @item_code, @item_code_dwhile @@fetch_status=0
begin
if exists(select * from #substitute_components where component_code=@item_code or component_code=@item_code_d)
begin
set @set_id=(select set_id from #substitute_components where component_code=@item_code or component_code=@item_code_d)

if not exists(select set_id from #substitute_components where component_code=@item_code)
begin
insert into #substitute_components (component_code, set_id) values(@item_code, @set_id)
update #substitute_sets set set_name=set_name+' '+@item_code where set_id=@set_id
end

if not exists(select set_id from #substitute_components where component_code=@item_code_d)
begin
insert into #substitute_components (component_code, set_id) values(@item_code_d, @set_id)
update #substitute_sets set set_name=set_name+' '+@item_code_d where set_id=@set_id
end
end
else
begin
insert into #substitute_sets (set_name) values (@item_code+' '+@item_code_d)
set @set_id=scope_identity()

insert into #substitute_components (component_code, set_id) values(@item_code, @set_id)
insert into #substitute_components (component_code, set_id) values(@item_code_d, @set_id)
end

fetch next from substitute_cursor into @item_code, @item_code_d
endclose substitute_cursor
deallocate substitute_cursorselect
coalesce(c.set_name, a.item_code), sum(a.qty)
from
t_a a left join #substitute_components b on
a.item_code=b.component_code
left join #substitute_sets c on
b.set_id=c.set_id
group by
coalesce(c.set_name, a.item_code)

Answer 6

Try the following data setcreate table ta(ITEM_CODE varchar(10),QTY int)
create table tb(ITEM_CODE varchar(10),ITEM_CODE_D varchar(10))
insert ta select 'a',200
union all select 'b',-100
union all select 'e', 0
union all select 'f',100
union all select 'c',55
union all select 'd',88insert tb select 'a','e'
union all select 'e','f'The result is not quite right.

Answer 7

本帖最后由 htl258 于 2010-07-16 09:47:53 编辑