MS SQL Server的COALESCE函数

ms sql server的coalesce函数是从一系列表达式中返回第一个not null的值。

 

检查[b],[q],[s],[t],[u]的值：

检查顺序[b]->[q]->[s]->[t]->[u]，只要一遇上not null时，即刻返回。

 

if object_id('tempdb.dbo.#part_summary') is not null drop table #part_summary

create table #part_summary (    
    [item] nvarchar(40),
    [b]decimal(18,2),
    [q]decimal(18,2),
    [s]decimal(18,2),
    [t]decimal(18,2),
    [u]decimal(18,2)
)

insert into #part_summary ([item],[b],[q],[s],[t],[u])
values ('098-ssss1-ws0098-5526',null,null,500.00,null,null),
       ('54f-art43-6545nn-2514',null,null,934.39,null,null),
       ('872-rtde3-q459pw-2323',null,346.43,null,452.44,null),
       ('b78-f1h2y-5456ud-2530',234.22,null,null,115.06,null),
       ('i32-gg443-qt0098-0001',null,null,423.65,null,null),
       ('i38-aa321-ws0098-0506',470.87,null,null,null,345.41),
       ('k38-12321-5456ud-3493',200.28,null,null,398.55,null),
       ('po0-7g7g7-jjy098-0077',null,871.33,543.00,null,null),
       ('rvc-43ase-h43qww-9753',null,564.96,null,null,555.19),
       ('x3c-sdewe-3er808-8764',null,607.88,null,null,null)

select [item],[b],[q],[s],[t],[u]  from #part_summary

select [item],coalesce([b],[q],[s],[t],[u]) as [result] from #part_summary

 

再列一个例子：

 

if object_id('tempdb.dbo.#part_summary') is not null drop table #part_summary

create table #part_summary (    
    [item] nvarchar(40),
    [b]decimal(18,2),
    [q]decimal(18,2),
    [s]decimal(18,2),
    [t]decimal(18,2),
    [u]decimal(18,2)
)

insert into #part_summary ([item],[b],[q],[s],[t],[u])
values ('54f-art43-6545nn-2514',null,null,null,null,934.39),
       ('872-rtde3-q459pw-2323',null,null,null,452.44,null),
       ('b78-f1h2y-5456ud-2530',null,null,115.06,null,null),
       ('i32-gg443-qt0098-0001',null,607.88,null,null,null),
       ('i38-aa321-ws0098-0506',470.87,null,null,null,null)

select [item],[b],[q],[s],[t],[u]  from #part_summary

select [item],coalesce([b],[q],[s],[t],[u]) as [result] from #part_summary