以下查询排除了所有产品,但是,我试图“仅当”连接表中的R.OPERATING_UNITS = 'WP'
和时排除产品PRODUCT_CAT = 'FUEL'
。我不知道如何条件。我想知道什么是最有效的方法来做到这一点。下面是查询的RESOURCE
,PRODUCT
表,也是希望的结果集。为了解释,我简化了表格和查询。
SELECT R.DEPTID,
R.FISCAL_YEAR,
sum(R.AMOUNT) total
FROM RESOURCE R
WHERE
R.PRODUCT_ID NOT IN (
SELECT PRODUCT_ID FROM PRODUCT WHERE PRODUCT_CAT='FUEL' )
group by R.FISCAL_YEAR,R.DEPTID
资源表
DPTID FISCAL_YEAR OPERATING_UNIT AMOUNT PRODUCT
PTT 2017 WP 1200 31000
PTT 2017 SP 3000 32000
PTT 2017 GP 1000 31000
PTT 2017 WP 1000 32000
FPP 2017 WP 1000 32000
FPP 2018 GP 2000 33000
FPP 2017 SP 1000 32000
FPP 2018 WP 2200 31000
产品表:
PRODUCT PRODUCT_CAT
31000 FUEL
32000 NON-FUEL
33000 MATERIAL
结果集。请注意,它在计算总和时忽略了 WP。
2017 PTT 5000 (igonred 1200 since operating unit=wp and product is 31000->FUEL but included wp and 32000)
2017 FPP 2000
2018 FPP 2000 (it did not consider the 2200 since operating unit=wp and product is 31000->FUEL)
更改以下语句后,WP 过滤器应该可以工作
NOT IN (
SELECT PRODUCT_ID FROM PRODUCT WHERE PRODUCT_CAT='FUEL' )
然后你可以过滤操作单元。
SELECT R.DEPTID,
R.FISCAL_YEAR,
sum(R.AMOUNT) total
FROM RESOURCE R
WHERE
r.OPERATING_UNIT = 'WG' and
R.PRODUCT_ID IN
(
SELECT PRODUCT_ID FROM PRODUCT WHERE PRODUCT_CAT='FUEL' )
group by R.FISCAL_YEAR,R.DEPTID
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句