SQL Server组通过吸收空值和空值 [英] SQL Server group by absorb null and empty values

问题描述

我有以下数据:

Id  Name     amount    Comments 
-------------------------------
1     n1     421762    Hello    
2     n2        421    Bye      
3     n2        262    null     
4     n2       5127    ''  

每个名称可能有也可能没有多余的行，这些行的注释为空或空.

Each name may or may not have extra rows with null or empty comments.

如何按名称和总和(金额)分组，以使其忽略/吸收分组中的空或空注释，并仅显示2个分组.

How can I group by name and sum(amount) such that it ignores/absorbs the null or empty comments in the grouping and shows me only 2 groups.

我想要的输出:

Id   Name     sum(amount)   Comments 
------------------------------------
1     n1         421762     Hello    
2     n2           5180     Bye 

我不知道这一点.

我希望这会忽略null/empty值，但是我总是以4组结尾

I hoped that would ignore the null/empty values but I always end up with 4 groups

select id, name, sum(amount), comments 
from table 
group by id, name, comments

推荐答案

除非它是GROUP BY子句的一部分或用于聚合，否则您不能在 SELECT 语句中包含字段.问题和期望的输出表明，应按名称对行进行分组，这意味着应汇总所有其他字段(ID，金额，注释).

You can't have a field in the SELECT statement unless it's part of the GROUP BY clause or used for aggregation. The question and desired output shows that the rows should be grouped by name, which means all other fields (ID,amount, comments) should be aggregated.

该问题未指定ID应该如何进行汇总，也没有指定应显示哪些注释.只有在2016年之前的所有SQL Server版本中，只能使用诸如MIN/MAX之类的函数来聚合字符串.SQLServer 2017添加了

The question doesn't specify how the IDs should be aggregated, or which comments should appear. Aggregating strings is only possible using functions like MIN/MAX in all SQL Server versions up to 2016. SQL Server 2017 added STRING_AGG to concatenate strings. In earlier versions people have to use one of many string aggregation techniques that may involve XML or SQLCLR functions.

在SQL Server版本中，所需的输出可以由

In SQL Server versions the desired output can be produced by

SELECT MIN(ID) as ID,name,sum(amount) as Amount, max(comment) as comments
from #table1 
group by name

这将产生所需的输出:

ID  name    Amount  comments
1   n1      421762  Hello
2   n2      5810    Bye

这假设只有一个非空注释.这个问题并没有说明什么不同.

This assumes that there is only one non-empty comment. The question doesn't specify something different though.

在SQL Server 2017中，可以将多个注释与STRING_AGG串联在一起:

In SQL Server 2017 multiple comments can be concatenated with STRING_AGG :

SELECT MIN(ID) as ID,name,sum(amount) as Amount, STRING_AGG(comment,' ') as comments
from table1 
group by name

给出问题的数据，这还将产生所需的输出.

Given the question's data, this will also produce the desired output.

ID  name    Amount  comments
1   n1      421762  Hello
2   n2      5810    Bye

这篇关于SQL Server组通过吸收空值和空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！