sql-server - SQL Server SUM IF 使用具有多个条件的窗口函数

Question

编辑：原始问题名称“SQL Server SUM 仅记录按某些值分组”。精致，因为现在知道要寻找什么。

我目前正在尝试使用OVERand来根据条件进行聚合PARITION BY，但是我不知道如何整合最后一个条件。

它应该做什么：如果每个CONTRACTID, VALIDFROM，VALIDTO列中有一个值“H104”，RENTALCOSTTYPEID 那么聚合所有AMOUNT具有RENTALCOSTTYPEID以“H”开头的值的值。ELSE 显示AMOUNT没有聚合。

我当前的代码在每个CONTRACTID,VALIDFROM有VALIDTO一个值“H104”时有效。但是，如果 per CONTRACTID, VALIDFROM，VALIDTO则没有值 'H104' 它给出 '0' 而不是AMOUNT 查看代码，它显然放置了 '0'，但是如果我更改此值，它会与有值的字段混淆'H104'。如何将此条件集成到以下代码中？

SELECT
CONTRACTID
,RENTALCOSTTYPEID
,VALIDFROM
,VALIDTO
,AMOUNT
,CASE 
    WHEN RENTALCOSTTYPEID = 'H104' 
    THEN SUM(CASE WHEN RENTALCOSTTYPEID LIKE 'H%' THEN AMOUNT ELSE 0  END) OVER (PARTITION BY CONTRACTID, VALIDFROM, VALIDTO) 
    ELSE SUM(CASE WHEN RENTALCOSTTYPEID LIKE 'H%' THEN 0 ELSE AMOUNT  END) OVER (PARTITION BY RENTALCOSTTYPEID, CONTRACTID, VALIDFROM, VALIDTO)
END AS TESTCOLUMN
FROM PMCCONTRACTLINE

列CURRENT包含我目前的结果

列ENDRESULT包含我想要的结果

+------------+------------------+------------+------------+---------+---------+-----------+
| CONTRACTID | RENTALCOSTTYPEID | VALIDFROM  | VALIDTO    | AMOUNT  | CURRENT | ENDRESULT |
+------------+------------------+------------+------------+---------+---------+-----------+
| HC018453   | H104             | 2020-07-01 | 2021-01-01 | 775.08  | 446.72  | 446.72    |
+------------+------------------+------------+------------+---------+---------+-----------+
| HC018453   | H110             | 2020-07-01 | 2021-01-01 | -328.36 | 0.00    | 0.00      |
+------------+------------------+------------+------------+---------+---------+-----------+
| HC018453   | V446             | 2020-07-01 | 2021-01-01 | 48.00   | 48.00   | 48.00     |
+------------+------------------+------------+------------+---------+---------+-----------+
| HC055533   | H105             | 2020-07-01 | 2021-01-01 | 330.00  | 0.00    | 330.00    |
+------------+------------------+------------+------------+---------+---------+-----------+
| HC055533   | H105H            | 2019-07-01 | 2020-06-30 | 330.00  | 0.00    | 330.00    |
+------------+------------------+------------+------------+---------+---------+-----------+
| HC103696   | H104             | 2020-06-03 | 2021-01-01 | 867.00  | 867.00  | 867.00    |
+------------+------------------+------------+------------+---------+---------+-----------+
| HC103696   | S468             | 2020-06-03 | 2021-01-01 | 2.00    | 2.00    | 2.00      |
+------------+------------------+------------+------------+---------+---------+-----------+
| HC103696   | S484             | 2020-06-03 | 2021-01-01 | 1.00    | 1.00    | 1.00      |
+------------+------------------+------------+------------+---------+---------+-----------+
| HC103696   | S488             | 2020-06-03 | 2021-01-01 | 0.50    | 0.50    | 0.50      |
+------------+------------------+------------+------------+---------+---------+-----------+

标签： sql-serversql-server-2012

Answer 1

您试图在同一行上显示聚合值和非聚合值。虽然在单个语句中实现这一点可能是可能的，但在一个查询中select组合几个 's 会更容易。select

例如，如果您在公用表表达式H%(CTE) 中计算其中有H104一行的rentalcosttypeid 行的总和，那么您可以稍后加入这些结果。

样本数据

declare @PMCCONTRACTLINE table
(
    CONTRACTID nvarchar(10),
    RENTALCOSTTYPEID nvarchar(5),
    VALIDFROM date,
    VALIDTO date,
    AMOUNT money
);

insert into @PMCCONTRACTLINE (CONTRACTID, RENTALCOSTTYPEID, VALIDFROM, VALIDTO, AMOUNT) values
('HC018453', 'H104',  '2020-07-01', '2021-01-01', 775.08 ),
('HC018453', 'H110',  '2020-07-01', '2021-01-01', -328.36),
('HC018453', 'V446',  '2020-07-01', '2021-01-01', 48.00  ),
('HC055533', 'H105',  '2020-07-01', '2021-01-01', 330.00 ),
('HC055533', 'H105H', '2019-07-01', '2020-06-30', 330.00 ),
('HC103696', 'H104',  '2020-06-03', '2021-01-01', 867.00 ),
('HC103696', 'S468',  '2020-06-03', '2021-01-01', 2.00   ),
('HC103696', 'S484',  '2020-06-03', '2021-01-01', 1.00   ),
('HC103696', 'S488',  '2020-06-03', '2021-01-01', 0.50   );

解决方案

定义为具有相同 ContractID的行的行cte_SumH104的结果集。H%H104

with cte_SumH104 as
(
    select cl.CONTRACTID, sum(cl.AMOUNT) as 'SUMH'
    from @PMCCONTRACTLINE cl
    where cl.RENTALCOSTTYPEID like 'H%'
      and exists (  select top 1 'x'
                    from @PMCCONTRACTLINE clh104
                    where clh104.CONTRACTID = cl.CONTRACTID
                      and clh104.RENTALCOSTTYPEID = 'H104' )
    group by cl.CONTRACTID
)
select  cl.*,
        s.SUMH,
        case
            when cl.RENTALCOSTTYPEID = 'H104' then s.SUMH
            when cl.RENTALCOSTTYPEID like 'H%' and s.SUMH is not null then 0
            else cl.AMOUNT
        end as 'TEST'
from @PMCCONTRACTLINE cl
left join cte_SumH104 s
    on s.CONTRACTID = cl.CONTRACTID;

结果

CONTRACTID  RENTALCOSTTYPEID  VALIDFROM   VALIDTO     AMOUNT    SUMH     TEST
----------- ----------------- ----------- ----------- --------- -------- --------
HC018453    H104              2020-07-01  2021-01-01  775,08    446,72   446,72
HC018453    H110              2020-07-01  2021-01-01  -328,36   446,72   0,00
HC018453    V446              2020-07-01  2021-01-01  48,00     446,72   48,00
HC055533    H105              2020-07-01  2021-01-01  330,00    NULL     330,00
HC055533    H105H             2019-07-01  2020-06-30  330,00    NULL     330,00
HC103696    H104              2020-06-03  2021-01-01  867,00    867,00   867,00
HC103696    S468              2020-06-03  2021-01-01  2,00      867,00   2,00
HC103696    S484              2020-06-03  2021-01-01  1,00      867,00   1,00
HC103696    S488              2020-06-03  2021-01-01  0,50      867,00   0,50