MySQL - 仅当所有行都不为空时才求和，否则返回null

Let's suppose the following table:

我们假设下表：

 X   VALUE
 1   2
 1   3
 2   NULL
 2   4

I want a result set grouped by X with the sum of VALUE, but only if all the rows related to each X value are not null.

我想要一个由X分组的结果集，其总和为VALUE，但前提是所有与每个X值相关的行都不为空。

Using the same example, the result must be:

使用相同的示例，结果必须是：

X   SUM(VALUE)
1   5

As you can see, X=2 is not selected due to the (2, NULL) tuple.

如您所见，由于（2，NULL）元组，未选择X = 2。

I want, whenever this is possible, without using subqueries.

我希望，只要有可能，不使用子查询。

Thank you very much!

非常感谢你！

3 个解决方案

#1

You can achieve that with:

您可以通过以下方式实现：

SELECT 
  x, 
  SUM(value) 
FROM 
  t 
GROUP BY 
  x 
HAVING 
  COUNT(value)=COUNT(*)

This will work following way: group values in normal way, but then compare entire count (so * points to that) with column count (which won't include NULL-s). If they are not equal, then there are NULL's and value shouldn't be included.

这将按以下方式工作：以正常方式对值进行分组，然后将整个计数（因此*指向该值）与列计数（不包括NULL-s）进行比较。如果它们不相等，则存在NULL，并且不应包括值。

#2

Another way is this. It may or may not be faster:

另一种方式是这样的。它可能会也可能不会更快：

SELECT X, IF(SUM(VALUE is NULL), NULL, SUM(VALUE)) as value
FROM table_name
group by X
having not(null <=> value);

Also, with this method, if you remove the having clause you get all rows, but with null values for the summed columns with nulls which you then could conditionally do something else with in your script.

此外，使用此方法，如果删除having子句，则会获得所有行，但是对于带有空值的求和列的空值，您可以在脚本中有条件地执行其他操作。

http://sqlfiddle.com/#!2/f769cc/18

#3

-1

This whould work:

这可以工作：

SELECT SUM(value) FROM test WHERE value IS NOT NULL GROUP BY X

#1