如何通过t-sql中的空值分组

Question

在SELECT语句中，我需要以某种方式知道Coverage列中是否有NULL;如果是这样，那么我希望它以不为null and Group的价值代替。

declare @TestTable table 
(
     ClaimNumber varchar(50), 
     Status varchar(50),
     Coverage varchar(50),
     Losses money
)
insert into @TestTable 
values ('WICCACA0000002', 'Open', 'Auto BIPD',5000),
       ('WICCACA0000003', 'Closed', 'Collision', 1000),
       ('WICCACA0000003', 'Closed', NULL, 2000)
select 
    ClaimNumber,
    Status,
    Coverage,
    SUM(Losses) as Losses 
from 
    @TestTable
group by 
    ClaimNumber, Status, Coverage

结果看起来像这样：

ClaimNumber     Status  Coverage    Losses
--------------------------------------------
WICCACA0000002  Open    Auto BIPD   5000.00
WICCACA0000003  Closed  NULL        2000.00
WICCACA0000003  Closed  Collision   1000.00

，但我需要结果如下：

ClaimNumber     Status  Coverage    Losses
--------------------------------------------
WICCACA0000002  Open    Auto BIPD   5000.00
WICCACA0000003  Closed  Collision   3000.00

Answer 1

我认为 coalesce()做你想要的：

select ClaimNumber, Status, coalesce(Coverage, 'Collision') as coverage
       SUM(Losses) as Losses 
from @TestTable
group by ClaimNumber, Status, coalesce(Coverage, 'Collision');

另外，如果Coverage中只有一个值(如您的示例数据中(，只需使用聚合函数：

select ClaimNumber, Status, max(Coverage) as coverage
       SUM(Losses) as Losses 
from @TestTable
group by ClaimNumber, Status;