有没有简单的方法(语法)来编写嵌套的case语句来理解它?

就我个人而言,我会这样写:

select
    case
    when isNull(NetRate_Quote_Insur_Quote_Locat.[Transaction], '') <> ''
            and NetRate_Quote_Insur_Quote_Locat.EndorsementNumber
              = NetRate_Quote_Insur_Quote.EndorsementNumber
    then
        case NetRate_Quote_Insur_Quote_Locat.[Transaction]
        when 'ADD'          then 'Add'
        when 'DELETED'      then 'Delete'
        when 'CHANGED'      then 'Change'
        when 'New Quote'    then 'Add'
        else
            case
            when tblQuotes.OriginalQuoteGuid is null
                or NetRate_Quote_Insur_Quote.TypeofBusiness = 'Reinstate'
            then 'Add'
            else
                case
                when tblQuotes.QuoteStatusID = 12
                then 'Delete'
                else 'No Change'
                end
            end
        end
    else
        case
        when tblQuotes.OriginalQuoteGuid is null
            or NetRate_Quote_Insur_Quote.TypeofBusiness = 'Reinstate'
        then 'Add'
        else
            case
            when tblQuotes.QuoteStatusID = 12
            then 'Delete'
            else 'No Change'
            end
        end
    end as TransactionName
from ...

但我会把一些箱子拿走:

select
    case
    when isNull(NetRate_Quote_Insur_Quote_Locat.[Transaction], '')
                in ('ADD','DELETE','CHANGED','New Quote')
            and NetRate_Quote_Insur_Quote_Locat.EndorsementNumber
              = NetRate_Quote_Insur_Quote.EndorsementNumber
    then
        case NetRate_Quote_Insur_Quote_Locat.[Transaction]
        when 'ADD'          then 'Add'
        when 'DELETED'      then 'Delete'
        when 'CHANGED'      then 'Change'
        when 'New Quote'    then 'Add'
        end
    else
        case
        when tblQuotes.OriginalQuoteGuid is null
            or NetRate_Quote_Insur_Quote.TypeofBusiness = 'Reinstate'
        then 'Add'
        else
            case
            when tblQuotes.QuoteStatusID = 12
            then 'Delete'
            else 'No Change'
            end
        end
    end as TransactionName
from ...

这种变化使 CASE WHEN tblQuotes.QuoteStatusID = 12 周围的逻辑 case 对于这两种情况,因为嵌套 搜查案件 表达:

case
  when IsNull( NetRate_Quote_Insur_Quote_Locat.[Transaction], '' ) <> '' and
    NetRate_Quote_Insur_Quote_Locat.EndorsementNumber = NetRate_Quote_Insur_Quote.EndorsementNumber
    then
      case NetRate_Quote_Insur_Quote_Locat.[Transaction]
        when 'ADD'       then 'Add'
        when 'DELETED'   then 'Delete'
        when 'CHANGED'   then 'Change'
        when 'New Quote' then 'Add'
        else
          case
            when tblQuotes.OriginalQuoteGuid is NULL or NetRate_Quote_Insur_Quote.TypeofBusiness = 'Reinstate'
              then 'Add'
            when tblQuotes.QuoteStatusID = 12 then 'Delete'
            else 'No Change' 
            end
        end
  else
    case
      when tblQuotes.OriginalQuoteGuid is NULL or NetRate_Quote_Insur_Quote.TypeofBusiness = 'Reinstate'
        then 'Add'
      when tblQuotes.QuoteStatusID = 12 then 'Delete' 
      else 'No Change' 
      end
    end as TransactionName

如果 NetRate_Quote_Insur_Quote_Locat.[Transaction] 只能列出值( 'ADD' ,请 'DELETED' 我是说, 'CHANGED' 和 'New Quote' )那么任何其他值都会导致 NULL 导致 else 条款。你可以用它 coalesce 要折叠大量重复代码:

Coalesce(
  case
    when IsNull( NetRate_Quote_Insur_Quote_Locat.[Transaction], '' ) <> '' and
      NetRate_Quote_Insur_Quote_Locat.EndorsementNumber = NetRate_Quote_Insur_Quote.EndorsementNumber
      then
        case NetRate_Quote_Insur_Quote_Locat.[Transaction]
          when 'ADD'       then 'Add'
          when 'DELETED'   then 'Delete'
          when 'CHANGED'   then 'Change'
          when 'New Quote' then 'Add'
          end,
  case
    when tblQuotes.OriginalQuoteGuid is NULL or NetRate_Quote_Insur_Quote.TypeofBusiness = 'Reinstate'
      then 'Add'
    when tblQuotes.QuoteStatusID = 12 then 'Delete' 
    else 'No Change' 
    end ) as TransactionName

当表或列名使事情变得非常水平时,我使用别名。因此,在您的“from”和“join”语句中,我将netrate quote\u insure quote别名为“nquote”,将“netrate quote\u insure quote\u locat”别名为“loc”。

除此之外,对于需要多行的内容,我喜欢colname=xyz语法。

另外,我喜欢“case”与“when”、“else”和“end”语句对齐,如果可行,“then”语句与这些rest不一致。

所以看起来是这样的:

select  TransactionName = 
            CASE 
            WHEN ISNULL(loc.[Transaction], '') <> '' AND loc.EndorsementNumber = nQuote.EndorsementNumber THEN 
                CASE loc.[Transaction]
                WHEN 'ADD' THEN 'Add'
                WHEN 'DELETED' THEN 'Delete'
                WHEN 'CHANGED' THEN 'Change'
                WHEN 'New Quote' THEN 'Add'
                ELSE 
                    CASE 
                    WHEN tblQuotes.OriginalQuoteGuid IS NULL OR nQuote.TypeofBusiness = 'Reinstate' THEN 'Add'
                    ELSE 
                        CASE WHEN tblQuotes.QuoteStatusID = 12 THEN 'Delete' 
                        ELSE 'No Change' 
                        END
                    END
                END
            ELSE 
                CASE 
                WHEN tblQuotes.OriginalQuoteGuid IS NULL OR nQuote.TypeofBusiness = 'Reinstate' THEN 'Add'
                ELSE 
                    CASE 
                    WHEN tblQuotes.QuoteStatusID = 12 THEN 'Delete' 
                    ELSE 'No Change' 
                    END
                END
            END 

另外,我发现将重复的case逻辑放入cross-apply语句中常常是有帮助的。因此,如果在join语句之后,可以添加如下内容:

cross apply (select 
    endorsed = 
        case 
        when ISNULL(loc.[Transaction], '') <> '' AND loc.EndorsementNumber = nQuote.EndorsementNumber then 1
        else 0 
        end,
    nullOrReinstate = 
        case 
        when tblQuotes.OriginalQuoteGuid IS NULL OR nQuote.TypeofBusiness = 'Reinstate' then 1
        else 0
        end
) ap

然后可以在逻辑中使用这些列。最后,如果我没有弄错的话,我相信你的案例陈述可能不会像这样:

select  TransactionName = 
            CASE 
            WHEN endorsed = 1 and loc.[Transaction] in ('add', 'deleted', 'changed') then loc.[transaction]
            WHEN endorsed = 1 and loc.[transaction] = 'New Quote' THEN 'Add'
            WHEN endorsed = 1 and nullOrReinstate = 1 THEN 'Add'
            WHEN endorsed = 1 and tblQuotes.QuoteStatusID = 12 THEN 'Delete' 
            WHEN nullOrReinstate = 1 THEN 'Add'
            WHEN tblQuotes.QuoteStatusID = 12 THEN 'Delete' 
            ELSE 'No Change' 
            END 

我敢肯定这些都是没必要的。我解决了这个问题,并认为不需要外部大小写,因为您要检查事务是否为非空,然后在嵌套的检查测试中检查其值。如果排除此部分,则两个外部条件相同,这意味着可以删除整个嵌套。我很确定它的评估结果如下。

总的来说你有很多 ELSE CASE xxx 子句,其中第二个case可以简单地作为更多when子句添加到原始case中,特别是如果不使用 CASE value 换成 CASE WHEN value= 语法。

这就是我认为所有人的决心:

CASE 
    WHEN NetRate_Quote_Insur_Quote_Locat.[Transaction]='ADD' THEN 'Add'
    WHEN NetRate_Quote_Insur_Quote_Locat.[Transaction]='DELETED' THEN 'Delete'
    WHEN NetRate_Quote_Insur_Quote_Locat.[Transaction]='CHANGED' THEN 'Change'
    WHEN NetRate_Quote_Insur_Quote_Locat.[Transaction]='New Quote' THEN 'Add'
    WHEN tblQuotes.OriginalQuoteGuid IS NULL OR NetRate_Quote_Insur_Quote.TypeofBusiness = 'Reinstate' THEN 'Add'
    WHEN tblQuotes.QuoteStatusID = 12 THEN 'Delete' 
    ELSE 'No Change' 
END as TransactionName

我可以帮你省几行

    case NetRate_Quote_Insur_Quote_Locat.[Transaction]
    when 'ADD'          then 'Add'
    when 'DELETED'      then 'Delete'
    when 'CHANGED'      then 'Change'

可以变成

    case when NetRate_Quote_Insur_Quote_Locat.[Transaction] in ('ADD', 'DELETED', 'CHANGED') then LEFT(NetRate_Quote_Insur_Quote_Locat.[Transaction], 6)