如何减去而不是像SQL函数一样添加Sum（） [英] How to minus instead of add in a Sum() like sql function

问题描述

我在sql语句中有一个group by子句，需要使用聚合函数减去每个组中的所有值，而不是像Sum（）函数那样添加。

I have a group by clause in a sql statement and need to use an aggregate function to minus all the values in each group instead of adding like the Sum() function.

ie

SELECT Sum(A) 
FROM (  
  SELECT 2 AS A
  UNION 
  SELECT 1) AS t1

.. so将求值2 + 1并返回3。

..so will evaluate 2+1 and return 3.

我需要某种方式进行2-1来返回1。

I need some way of doing 2-1 to return 1.

希望这是有道理的。我能想到的唯一方法就是使用CLR集成来创建自己的聚合函数。

Hope this makes sense. Only way I can think of doing this would be to use CLR integration to make my own aggregate function.

还有其他想法吗？

推荐答案

根据您的问题，您还不确定要做什么。如果希望所有值的总和都为负，则可以执行SUM（）并乘以-1以返回负结果。

From your question it isn't exactly clear what you want to do. If you want the sum of all the values as if they were negative then you can just perform a SUM(), and multiply by -1 to return a negative result.

在您的示例中，您似乎想要获取表中第一行的总和，减去所有其他值。因此，如果您将值设置为10、15、5、20，则需要：10-15-5-20。该值与10-（15 + 5 + 20）相同。您可以使用LIMIT获得表中的第一行。我们还将需要用于下一阶段的主键：

In your example it looks like you want to get the sum of the first row in the table, minus all the other values. So if you had the values 10, 15, 5, 20 you'd want: 10 - 15 - 5 - 20. This value is the same as 10 - (15 + 5 + 20). You can get the first row in a table using LIMIT. We'll also need the primary key for the next stage:

SELECT primary_key AS pk, field FROM table LIMIT 1;

我们可以通过排除上一行来获得所有其他行的总和：

We can get the sum of all the other rows by excluding the above one:

SELECT SUM(field) FROM table WHERE primary_key != pk;

然后可以从第一个值中减去第二个值。

You can then subtract the second value from the first.

这篇关于如何减去而不是像SQL函数一样添加Sum（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！