MySQL-仅在所有行都不为null时求和，否则返回null [英] MySQL - Sum only if all rows are not null, else return null

问题描述

让我们假设下表:

 X   VALUE
 1   2
 1   3
 2   NULL
 2   4

仅当与每个X值相关的所有行都不为空时，我才希望将结果集按X与VALUE的总和分组.

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.

使用相同的示例，结果必须为:

Using the same example, the result must be:

X   SUM(VALUE)
1   5

如您所见，由于(2, NULL)元组，未选择X=2.

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

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

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

非常感谢！

推荐答案

您可以使用以下方法实现:

You can achieve that with:

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

这将以下列方式工作:以常规方式对值进行分组，但是然后将整个计数(因此*指向该点)与列计数(不包括NULL -s)进行比较.如果它们不相等，则为NULL，并且不应包含该值.

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.

这篇关于MySQL-仅在所有行都不为null时求和，否则返回null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！