根据记录排除项目

Question

以下查询不包括所有产品，但是，我仅在R.OPERATING_UNITS = 'WP'时才尝试排除产品。我不确定是否需要在Where子句中包含案例语句。我想知道什么是最好的有效方法。以下是查询，RESOURCE表以及所需的结果集。为了解释，我简化了表和查询。

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  
PTT         2017        WP      1200
PTT         2017        SP      3000
PTT         2017        GP      1000
FPP         2017        WP      1000
FPP         2018        GP      2000
FPP         2017        SP      1000
FPP         2018        SP      2000

结果集。请注意，计算总和时，它忽略了WP。

2017    PTT 4000
2017    FPP 1000
2018    FPP 4000    

Answer 1

create table #resource
(
    DPTID varchar(10)
    ,FISCAL_YEAR varchar(10)
    ,OPERATING_UNIT varchar(10)
    ,AMOUNT int
)
insert into #resource
select 'PTT','2017','WP', 1200 union all
select 'PTT','2017','SP', 3000 union all
select 'PTT','2017','GP', 1000 union all
select 'FPP','2017','WP', 1000 union all
select 'FPP','2018','GP', 2000 union all
select 'FPP','2017','SP', 1000 union all
select 'FPP','2018','SP', 2000
select * from #resource
select FISCAL_YEAR,DPTID, SUM(AMOUNT) as Amount
from #resource
where OPERATING_UNIT <> 'WP'
group by FISCAL_YEAR,DPTID
order by FISCAL_YEAR,Amount desc

drop table #resource

输出：

FISCAL_YEAR DPTID   Amount
2017        PTT     4000
2017        FPP     1000
2018        FPP     4000

Answer 2

我相信您只需要这样的所有非WP产品：

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')
    OR R.OPERATING_UNITS <> 'WP' -- <<<========
GROUP BY R.DEPTID, R.FISCAL_YEAR

当然，您也可以通过加入来处理这一点，因为我认为内部联盟将起作用，并且是一对一的关系：

SELECT R.DEPTID, R.FISCAL_YEAR, SUM(R.AMOUNT) total
FROM RESOURCE R INNER JOIN PRODUCT P ON P.PRODUCT_ID = R.PRODUCT_ID
WHERE
    P.PRODUCT_CAT <> 'FUEL' OR R.OPERATING_UNITS <> 'WP'
    -- by DeMorgan's Law this is equivalent:
    -- NOT (P.PRODUCT_CAT = 'FUEL' AND R.OPERATING_UNITS = 'WP')
GROUP BY R.DEPTID, R.FISCAL_YEAR