27,580
社区成员
发帖
与我相关
我的任务
分享
select
a.ffgcode,coalesce(b.fup,c.fup,0)fup
from
#x_FgMst a
left join
#x_FgUp b on a.ffgcode=b.ffgcode
left join
(select a.[fpfgcode],fup=sum(a.fqty*b.fup) from #x_BOMSet a left join #x_FgUp b on a.ffgcode=b.ffgcode group by a.[fpfgcode] having min(isnull(b.fup,0))>0)c --改一下連接方法left join
on a.ffgcode=c.[fpfgcode]
/*
ffgcode fup
------------------------------ ---------------------------------------
A 1.000000000000
B 0.000000000000
C 1.000000000000
(3 個資料列受到影響)
*/
select
a.ffgcode,coalesce(c.fup,b.fup,0)fup
from
#x_FgMst a
left join
#x_FgUp b on a.ffgcode=b.ffgcode
left join
(select a.[fpfgcode],fup=sum(a.fqty*b.fup) from #x_BOMSet a left join #x_FgUp b on a.ffgcode=b.ffgcode group by a.[fpfgcode] having isnull(min(b.fup),0)>0)c --改一下連接方法left join
on a.ffgcode=c.[fpfgcode]
/*
ffgcode fup
------------------------------ ---------------------------------------
A 3.000000000000
B 1.000000000000
C 1.000000000000
(3 個資料列受到影響)
*/
create table #x_FgMst --产品表
(
ffgcode varchar(30) not null --套件
)
create table #x_FgUp --单价表
(
ffgcode varchar(30) not null,--品号(可以是分件或者套件)
fup decimal(18,6) null
)
create table #x_BOMSet --配套表
(
fpfgcode varchar(30) not null,--套件
ffgcode varchar(30) not null, --分件
fqty decimal(18,6) null --配套数量
)
truncate table #x_FgMst
insert into #x_FgMst(ffgcode)
select 'A'
UNION
SELECT 'B'
UNION
select 'C'
truncate table #x_FgUp
insert into #x_FgUp(ffgcode,fup)
select 'A1',1
UNION
SELECT 'A2',1
UNION
select 'A3',1
UNION
select 'B1',1
UNION
select 'C1',1
UNION
SELECT 'A',1
truncate table #x_BOMSet
insert into #x_BOMSet(fpfgcode,ffgcode,fqty)
select 'A','A1',1
UNION
SELECT 'A','A2',1
UNION
select 'A','A3',1
UNION
select 'B','B1',1
UNION
SELECT 'B','B2',1
UNION
select 'C','C1',1
select
a.ffgcode,coalesce(c.fup,b.fup,0)fup
from
#x_FgMst a
left join
#x_FgUp b on a.ffgcode=b.ffgcode
left join
(select a.[fpfgcode],fup=sum(a.fqty*b.fup) from #x_BOMSet a join #x_FgUp b on a.ffgcode=b.ffgcode group by a.[fpfgcode] having isnull(min(b.fup),0)>0)c
on a.ffgcode=c.[fpfgcode]
ffgcode fup
------------------------------ ---------------------------------------
A 3.000000000000
B 1.000000000000
C 1.000000000000
(3 個資料列受到影響)
create table #x_FgMst --产品表
(
ffgcode varchar(30) not null --套件
)
create table #x_FgUp --单价表
(
ffgcode varchar(30) not null,--品号(可以是分件或者套件)
fup decimal(18,6) null
)
create table #x_BOMSet --配套表
(
fpfgcode varchar(30) not null,--套件
ffgcode varchar(30) not null, --分件
fqty decimal(18,6) null --配套数量
)
select a.ffgcode ,isnull(isnull(b.p,c.p),d.p)
from #x_FgMst a left join #x_fgup b on a.ffgcode = b.ffgcode
left join (select ffgcode,p=0 from #x_bomset group ffgcode) c on a.ffgcode = c.ffgcode
left join (select fgfgcode,p=0 from #x_bomset d1
where exists(select 1 from #x_fgup where fup = 0 and ffgcode in
(select ffgcode from #x_bomset where fgfgcode = d1.fgfgcode))
group by fgfgcode )
d on a.ffgcode = d.ffgcode
left join (select fgfgcode,p=(select sum(fup) from #x_fgup where ffgcode in
(select ffgcode from #x_bomset where fgfgcode = e1.fgfgcode) )
from #x_bomset e1 group by fgfgcode )
e on a.ffgcode = e.ffgcode
select
a.ffgcode,coalesce(c.fup,b.fup,0)fup
from
#x_FgMst a
left join
#x_FgUp b on a.ffgcode=b.ffgcode
left join
(select a.[ffgcode],fup=sum(a.fqty*b.fup) from #x_BOMSet a join #x_FgUp b on a.ffgcode=b.ffgcode
where exists(select 1 from #x_FgUp c where not exists(select 1 from #x_BOMSet where fpfgcode=a.fpfgcode and ffgcode=c.ffgcode)) group by a.[ffgcode])c
on a.ffgcode=c.ffgcode
/*
ffgcode fup
------------------------------ ---------------------------------------
A 1.000000000000
B 0.000000000000
C 0.000000000000
(3 個資料列受到影響)
*/