SELECT '貨款部分' as cls,
stlchk.stlchkno,
stlchk.mkedat,
(select empdes from emp where empid=stlchk.mkr) as mkr,
gdsbuy.srcbllno,
round(sum(gdsbuy.amt),2) as bivcdtl_amt,
round(sum(gdsbuy.patamt),2) as bivcdtl_patamt,
(select distinct '['+rtrim(bll.bllid)+']'+blldes from bll where bll.bllid=gdsbuy.bllid) as blldes,
gdsbuy.ctno,
(select openaccbnkr from prv where prvid=stlchk.prvid) as op,
(select actno from prv where prvid=stlchk.prvid) as actno,
(select bnk from prv where prvid=stlchk.prvid) as abnk,
(select dptid from dpt where dptid in (select savdptid from stkin where stkinno=gdsbuy.srcbllno)) as fd,
(select '['+rtrim(prvid)+']'+prvdes from prv where prvid = stlchk.prvid) as prvdes
FROM ((stlchk inner join stlbll on stlchk.stlchkno=stlbll.stlchkno) inner join bivc on bivc.bivcno = stlbll.lnkstlno) inner join gdsbuy on bivc.bivcno=gdsbuy.lnkivcno
WHERE stlchk.prvid like :as_prvid and
stlchk.mkedat>= :as_bdate and stlchk.mkedat<= :as_edate and stlchk.mkr in (select empid from emp where dptid <>'311') and
gdsbuy.bstlflg='y'
GROUP BY stlchk.stlchkno,
stlchk.prvid,
stlchk.mkedat,
stlchk.mkr,
gdsbuy.bllid,
gdsbuy.srcbllno,
gdsbuy.ctno
union
SELECT '費用部分' as cls,
stlchk.stlchkno,
stlchk.mkedat,
(select empdes from emp where empid=stlchk.mkr) as mkr,
'' as srcbllno,
0 as bivcdtl_amt,
sum(stlchkdtl.feeamt) as bivcdtl_patamt,
(select '['+rtrim(feeitm.feeitmid)+']'+feeitm.feeitmdes from feeitm where feeitm.feeitmid = stlchkdtl.feeitmid ) as blldes,
'' as ctno,
'' as op,
'' as actno,
'' as abnk,
'' as fd,
'' as prvdes
FROM stlchk,
stlchkdtl
WHERE stlchk.prvid like :as_prvid and stlchkdtl.stlchkno=stlchk.stlchkno and
stlchk.mkedat>= :as_bdate and stlchk.mkedat<= :as_edate and stlchk.mkr in (select empid from emp where dptid <>'311')
group by stlchk.stlchkno,
stlchk.mkedat,
stlchk.mkr,
stlchkdtl.feeitmid
小弟實在沒啥分了...跪謝各位大神！
uj5u.com熱心網友回復：
in 換成exists union 換成 union alluj5u.com熱心網友回復：
謝謝，一會兒我先試試uj5u.com熱心網友回復：
夠復雜的哈哈
uj5u.com熱心網友回復：
是不是還需要表結構
uj5u.com熱心網友回復：
更慢了～uj5u.com熱心網友回復：
可以先把對stlchk的表的條件進行處理之后 再和其他的表進行關聯然后減少子查詢 用Left join 替代
uj5u.com熱心網友回復：
對where條件里出現的欄位,做索引轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/21276.html
標籤：基礎類
下一篇：pb9能顯示韓文嗎