SQL查询-多年合计数量

Question

SQL问题-我的数据包含按会计期间(1-12)和会计年度销售的产品和数量。以下SQL返回特定年份按期间计算的总和数量：

 Select   ABS(Sum(QTY)) Total, FISCPERIOD
    From dbo.ICXLHIS
    where FISCYEAR=2016 and ITEMNUM='LBCNIQTL01100506WCP' and APP = 'PO'
    Group by FISCPERIOD

我想要2016和2017年的数据，但是，当我将2017添加到Where子句中时，它返回了不正确的总和数量值：

Select   ABS(Sum(QTY)) Total, FISCPERIOD, FISCYEAR
From dbo.ICXLHIS
where FISCYEAR=2016 and FISCYEAR=2017 and ITEMNUM='LBCNIQTL01100506WCP' and 
APP = 'PO'
Group by FISCPERIOD, FISCYEAR

不确定我做错了什么

Answer 1

我猜FISCALYEAR不可能同时是2016 和 2017。您可能希望这样更改您的SQL：

Select   ABS(Sum(QTY)) Total, FISCPERIOD, FISCYEAR
From dbo.ICXLHIS
where FISCYEAR IN (2016, 2017) and 
      ITEMNUM='LBCNIQTL01100506WCP' and 
      APP = 'PO'
Group by FISCPERIOD, FISCYEAR

Answer 2

也许这就是where FISCYEAR=2016 and FISCYEAR=2017的问题

尝试这样做，where FISCYEAR between (2016 and 2017)

我希望对你有所帮助！