总额和部分金额

问题描述：

我目前正在使用SSMS。我正在提取数据，并试图找到两个不同的列来总结价格。两列'ChangeSpend'和'TotalSpend'都引用同一列，这就是我遇到问题的地方。总额和部分金额

我想要ChangeSpend返回以V.Ch％开头的所有代码（因此它们排除所有其他代码）和TotalSpend以总计每个收据的所有代码的总和。

这里是我当前的代码：

SELECT 
     Receipt 
     ,ReceiptCode 
     ,ReceiptAmount 
     ,sum(ReceiptAmount) over (Partition by Receipt) as TotalSpend 
     ,(CASE WHEN ReceiptCode = 'V.Ch%' then sum(ReceiptAmount) 
              over (Partition by Receipt) 
     ELSE 0 
     END) as ChangeSpend 
    FROM tableA 
    LEFT OUTER JOIN tableB 
    on A.Receipt = B.Receipt 
    WHERE ReceiptCode LIKE 'V.%' 
    ORDER BY Receipt

然而，我的查询目前打印此：

Receipt ReceiptCode ReceiptAmount TotalSpend ChangeSpend 
1   v.cha   5    20   0   
1   v.rt   2    20   0 
1   v.chb   6    20   0 
1   v.abc   7    20   0 
2   v.cha   20    21   0 
2   v.abc   1    21   0 
3   v.cha   4    14   0 
3   v.chb   1    14   0 
3   v.tye   7    14   0 
3   v.chs   2    14   0

而且我想它打印：

Receipt ReceiptCode ReceiptAmount TotalSpend ChangeSpend 
1   v.cha   5    20   11   
1   v.rt   2    20   11 
1   v.chb   6    20   11 
1   v.abc   7    20   11 
2   v.cha   20    21   20 
2   v.abc   1    21   20 
3   v.cha   4    14   7 
3   v.chb   1    14   7 
3   v.tye   7    14   7 
3   v.chs   2    14   7

感谢任何帮助

'CASE当收到代码像'V.Ch％'

另外，为这个 –

编写子查询是有意义的当我这样做时，它改变了在收据旁边出现0的问题，但它不会将它们相加（它只会在相应行中打印5和6收据1） – nola94

答

尝试

,SUM(CASE WHEN ReceiptCode LIKE 'V.Ch%' THEN ReceiptAmount ELSE 0 END) 
OVER (Partition by Receipt) 
    AS ChangeSpend

是的这个工作！ – nola94

答

你必须把SUM外壳外侧，而不是其他的方式：

SUM(CASE WHEN SomeCondition=true THEN MyColumn ELSE 0 END)

我明白你在说什么，但是当我试图写 'sum（CASE WHEN ReceiptCode ='V.Ch％'then（ ReceiptAmount）（由收据分区） ELSE 0 END）as ChangeSpend' 我收到关于单词'over'的错误？ – nola94

将'OVER ...'放在SUM（）'之外。 'END'后' –

答

SUM(
CASE WHEN ReceiptCode like 'V.Ch%' then ReceiptAmount ELSE 0 END) as ChangeSpend

答

这可能会帮助：

Create Table Payment(
    Receipt Int, 
    ReceiptCode VARCHAR(10), 
    ReceiptAmount decimal) 


    Insert Into Payment 
    Values 
    (1, 'v.cha', 5), 
    (1, 'v.rt', 2), 
    (1, 'v.chb', 6), 
    (1, 'v.abc', 7), 
    (2, 'v.cha', 20), 
    (2, 'v.abc', 1), 
    (3, 'v.cha', 4), 
    (3, 'v.chb', 1), 
    (3, 'v.the', 7), 
    (3, 'v.chs', 2); 

    SELECT * , 
     SUM(ReceiptAmount) OVER (PARTITION BY Receipt) AS TotalSpend , 
     SUM(IIF(ReceiptCode LIKE 'v.ch%',ReceiptAmount,0)) OVER (PARTITION 
     BY Receipt) AS ChangeSpend 
    FROM payment;

结果：

相关推荐