sql - 如何用相同 PolicyNumber 的非空值替换空字符串

Question

ClaimType目标是用每个不为空的值替换列中的空值，CoverageType并且PolicyNumber

出于某种原因，我写的 SELECT 语句没有给我想要的结果。在我的 WHERE 子句中我指定：

 where  
             PolicyNumber = c.PolicyNumber 
            and CoverageType = c.CoverageType  

但是为什么对于PolicyNumber00002 我有 CPL 的“出现” CoverageType？

我希望看到的是Occurrence-Pollution：

我在这里想念什么？

declare @ClaimType table (PolicyNumber varchar(50), QuoteID int,  CoverageType varchar(50), ClaimType varchar(100))

insert into @ClaimType values   ('00001',1, 'CGL', ' '),
                                ('00001',2, 'CGL', 'Occurrence'),
                                ('00002',1, 'CPL', ' '),
                                ('00002',2, 'CPL', 'Occurrence-Pollution')

select PolicyNumber,
        QuoteID,
        CoverageType,
        ClaimType,
        case when ClaimType = ' ' then (
                                        select top 1 ClaimType  
                                        from @ClaimType c 
                                        where   
                                                 PolicyNumber = c.PolicyNumber 
                                                and CoverageType = c.CoverageType 
                                        order by QuoteID desc
                                        ) 
                                        else ClaimType End as ClaimType1
from @ClaimType
go

标签： sqlsql-servertsql

Answer 1

这是你想要的吗？

select ct.*,
       coalesce(nullif(ltrim(ClaimType), ''),
                max(ClaimType) over (partition by PolicyNumber, CoverageType)
               ) as ClaimType_2
from @ClaimType ct;