select wlbh,wlmc,wlms,zt, (select psdl from ck_pd where wlbh=ck_cc.wlbh and zt=ck_cc.zt) pdsl,ccsl from ck_cc
select wlbh,wlmc,wlms,zt, (select pdsl from ( select '100001' as wlbh,300 as pdsl,'良品' as zt union select '100001' as wlbh,230 as pdsl,'不良品' as zt union select '100002' as wlbh,3100 as pdsl,'不良品' as zt union select '100003' as wlbh,1230 as pdsl,'良品' as zt union select '100004' as wlbh,2130 as pdsl,'不良品' as zt union select '100004' as wlbh,2310 as pdsl,'良品' as zt union select '100005' as wlbh,2301 as pdsl,'良品' as zt union select '100006' as wlbh,3230 as pdsl,'不良品' as zt ) ck_pd where wlbh=ck_cc.wlbh and zt=ck_cc.zt) pdsl,ccsl from ( select '100001' as wlbh,'刀棒' as wlmc,'100*300*2.5六角' as wlms,'良品' as zt,500 as ccsl union select '100001' as wlbh,'刀棒' as wlmc,'100*300*2.5六角' as wlms,'不良品' as zt,1000 as ccsl union select '100002' as wlbh,'刀片' as wlmc,'刀片100*2' as wlms,'良品' as zt,2300 as ccsl union select '100003' as wlbh,'面盖' as wlmc,'ABS塑胶' as wlms,'良品' as zt,100 as ccsl union select '100004' as wlbh,'底盖' as wlmc,'ABS塑胶' as wlms,'良品' as zt,20 as ccsl union select '100005' as wlbh,'帖纸' as wlmc,'QC标帖' as wlms,'良品' as zt,450 as ccsl union select '100005' as wlbh,'帖纸' as wlmc,'QC标帖' as wlms,'不良品' as zt,300 as ccsl union select '100006' as wlbh,'扎带' as wlmc,'耗材' as wlms,'良品' as zt,100 as ccsl ) ck_cc
select distinct * from ( select a.wlbh,b.wlmc,b.wlms,a.zt,a.pdsl,a.ccsl from (select wlbh,zt,pdsl,ccsl from ( select wlbh,zt,sum(pdsl) as pdsl,sum(ccsl) as ccsl from ( select wlbh,zt,0 as pdsl,ccsl from ck_cc union all select wlbh,zt,pdsl,0 as ccsl from ck_pd ) a group by wlbh,zt ) a where pdsl<>0 ) a join ck_cc b on a.wlbh=b.wlbh ) a order by wlbh,zt --------------------------------------- 100001 刀棒 100*300*2.5六角 不良品 230 1000 100001 刀棒 100*300*2.5六角 良品 300 500 100002 刀片 刀片100*2 不良品 3100 0 100003 面盖 ABS塑胶 良品 1230 100 100004 底盖 ABS塑胶 不良品 2130 0 100004 底盖 ABS塑胶 良品 2310 20 100005 帖纸 QC标帖 良品 2301 450 100006 扎带 耗材 不良品 3230 0 (所影响的行数为 8 行)
select pd.wlbh, cc.wlmc, cc.wlms, pd.zt, pd.pdsl, cc.ccsl from ck_pd pd, ck_cc cc where pd.wlbh = cc.wlbh and pd.zt = cc.zt union all select pd.wlbh, cc.wlmc, cc.wlms, pd.zt, pd.pdsl, 0 as ccsl from ck_pd pd, ck_cc cc where pd.wlbh = cc.wlbh and cc.zt = '良品' and not exists (select * from ck_cc c where c.wlbh = pd.wlbh and c.zt = pd.zt) order by 1這是DELPHI K.TOP上的FISHMAN給我的回複
(select psdl from ck_pd where wlbh=ck_cc.wlbh and zt=ck_cc.zt)
pdsl,ccsl from ck_cc
(select pdsl from
( select '100001' as wlbh,300 as pdsl,'良品' as zt
union select '100001' as wlbh,230 as pdsl,'不良品' as zt
union select '100002' as wlbh,3100 as pdsl,'不良品' as zt
union select '100003' as wlbh,1230 as pdsl,'良品' as zt
union select '100004' as wlbh,2130 as pdsl,'不良品' as zt
union select '100004' as wlbh,2310 as pdsl,'良品' as zt
union select '100005' as wlbh,2301 as pdsl,'良品' as zt
union select '100006' as wlbh,3230 as pdsl,'不良品' as zt
)
ck_pd where wlbh=ck_cc.wlbh and zt=ck_cc.zt)
pdsl,ccsl from
( select '100001' as wlbh,'刀棒' as wlmc,'100*300*2.5六角' as wlms,'良品' as zt,500 as ccsl
union select '100001' as wlbh,'刀棒' as wlmc,'100*300*2.5六角' as wlms,'不良品' as zt,1000 as ccsl
union select '100002' as wlbh,'刀片' as wlmc,'刀片100*2' as wlms,'良品' as zt,2300 as ccsl
union select '100003' as wlbh,'面盖' as wlmc,'ABS塑胶' as wlms,'良品' as zt,100 as ccsl
union select '100004' as wlbh,'底盖' as wlmc,'ABS塑胶' as wlms,'良品' as zt,20 as ccsl
union select '100005' as wlbh,'帖纸' as wlmc,'QC标帖' as wlms,'良品' as zt,450 as ccsl
union select '100005' as wlbh,'帖纸' as wlmc,'QC标帖' as wlms,'不良品' as zt,300 as ccsl
union select '100006' as wlbh,'扎带' as wlmc,'耗材' as wlms,'良品' as zt,100 as ccsl
) ck_cc
当CK_PD的为不良品且CK_CC中无不良品时,出错
要求的结果为
100006 扎带 耗材 不良品 3230 0
实际为
100006 扎带 耗材 良品 NULL 100
select a.wlbh,b.wlmc,b.wlms,a.zt,a.pdsl,a.ccsl from
(select wlbh,zt,pdsl,ccsl from (
select wlbh,zt,sum(pdsl) as pdsl,sum(ccsl) as ccsl from (
select wlbh,zt,0 as pdsl,ccsl from ck_cc
union all
select wlbh,zt,pdsl,0 as ccsl from ck_pd
) a
group by wlbh,zt
) a where pdsl<>0
) a
join ck_cc b on a.wlbh=b.wlbh
) a order by wlbh,zt
---------------------------------------
100001 刀棒 100*300*2.5六角 不良品 230 1000
100001 刀棒 100*300*2.5六角 良品 300 500
100002 刀片 刀片100*2 不良品 3100 0
100003 面盖 ABS塑胶 良品 1230 100
100004 底盖 ABS塑胶 不良品 2130 0
100004 底盖 ABS塑胶 良品 2310 20
100005 帖纸 QC标帖 良品 2301 450
100006 扎带 耗材 不良品 3230 0
(所影响的行数为 8 行)
http://community.csdn.net/Expert/topic/3713/3713579.xml?temp=.253628
領分,謝謝!
本帖繼續....
cc.wlmc,
cc.wlms,
pd.zt,
pd.pdsl,
cc.ccsl
from ck_pd pd,
ck_cc cc
where pd.wlbh = cc.wlbh
and pd.zt = cc.zt
union all
select pd.wlbh,
cc.wlmc,
cc.wlms,
pd.zt,
pd.pdsl,
0 as ccsl
from ck_pd pd,
ck_cc cc
where pd.wlbh = cc.wlbh
and cc.zt = '良品'
and not exists (select * from ck_cc c where c.wlbh = pd.wlbh and c.zt = pd.zt)
order by 1這是DELPHI K.TOP上的FISHMAN給我的回複