如何在指定日期范围内仅汇总PRICE列的正值至计算列

问题描述 #

我有一张名为Tracking的表，包含NAMES、DATES和PRICE列，其中PRICE的值有正有负。需要按Name分组汇总，展示以下字段：

• CurTotal：当年全年的PRICE总和
• PreMonth：当年截至上月末的PRICE总和
• Curpay：当年截至上月末的PRICE正值总和

现有查询已实现前三个字段，但Curpay会汇总正负值，尝试用CASE表达式修改时报错。

可正常运行但不符合需求的代码 #

SELECT 
    Tracking.Name, 
    SUM(IIF(tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                              AND DateSerial(YEAR(DATE()), 12, 31), Tracking.Price, 0)) AS CurTotal, 
    SUM(IIF(tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                              AND DateSerial(YEAR(DATE()), MONTH(DATE()) - 1, 31), tracking.price, 0)) AS PreMonth, 
    SUM(IIF(tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                              AND DateSerial(YEAR(DATE()), MONTH(DATE()) - 1, 31), tracking.price, 0)) AS Curpay
FROM  
    Tracking
WHERE 
    (((Tracking.Date) BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                          AND DateSerial(YEAR(DATE()), 12, 31)))
GROUP BY 
    Tracking.Name
ORDER BY 
    Tracking.Name;

报错的尝试代码 #

SELECT Tracking.Name, 
   Sum(IIf(tracking.Date Between DateSerial(Year(Date()),1,1) 
         And DateSerial(Year(Date()),12,31),Tracking.Price,0)) AS CurTotal, 
   Sum(IIf(tracking.Date Between DateSerial(Year(Date()),1,1) 
         And DateSerial(Year(Date()),Month(Date())-1,31),tracking.price,0)) AS PreMonth,
   Sum(CASE WHEN IIf(tracking.Date Between DateSerial(Year(Date()),1,1) 
                And DateSerial(Year(Date()),Month(Date())-1,31), tracking.price ,0 )> 0, THEN 
          IIf(tracking.Date Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),Month(Date())-1,31), tracking.price ,0) 
       ELSE 0 
       END)AS Curpay
FROM Tracking
WHERE (((Tracking.Date) Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)))
GROUP BY Tracking.Name
ORDER BY Tracking.Name;

错误原因及修正方案 #

错误点 #

报错代码的CASE表达式存在语法错误：WHEN子句的条件后多了一个逗号，且嵌套IIF完全没必要，反而增加了逻辑复杂度。

修正后的查询（推荐用IIF，更适配Access） #

直接在IIF里同时判断日期范围和PRICE是否为正，只有同时满足时才取PRICE值，否则取0：

SELECT 
    Tracking.Name, 
    SUM(IIF(tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                              AND DateSerial(YEAR(DATE()), 12, 31), Tracking.Price, 0)) AS CurTotal, 
    SUM(IIF(tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                              AND DateSerial(YEAR(DATE()), MONTH(DATE()) - 1, 31), tracking.price, 0)) AS PreMonth, 
    SUM(IIF(tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                              AND DateSerial(YEAR(DATE()), MONTH(DATE()) - 1, 31) 
                              AND tracking.Price > 0, tracking.price, 0)) AS Curpay
FROM  
    Tracking
WHERE 
    Tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                          AND DateSerial(YEAR(DATE()), 12, 31)
GROUP BY 
    Tracking.Name
ORDER BY 
    Tracking.Name;

用CASE修正语法的版本 #

如果坚持用CASE，修正语法并简化条件：

SELECT 
    Tracking.Name, 
    SUM(IIF(tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                              AND DateSerial(YEAR(DATE()), 12, 31), Tracking.Price, 0)) AS CurTotal, 
    SUM(IIF(tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                              AND DateSerial(YEAR(DATE()), MONTH(DATE()) - 1, 31), tracking.price, 0)) AS PreMonth, 
    SUM(CASE 
          WHEN tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
               AND DateSerial(YEAR(DATE()), MONTH(DATE()) - 1, 31) 
               AND tracking.Price > 0 THEN tracking.price
          ELSE 0
        END) AS Curpay
FROM  
    Tracking
WHERE 
    Tracking.Date BETWEEN DateSerial(YEAR(DATE()), 1, 1) 
                          AND DateSerial(YEAR(DATE()), 12, 31)
GROUP BY 
    Tracking.Name
ORDER BY 
    Tracking.Name;

内容的提问来源于stack exchange，提问作者Mark Cone