sqlserver CASE WHEN语句示例

简单CASE WHEN 语句 case when 回复确认=’’ then ‘未完成’ when 回复确认=‘5’ then ‘完成’ end as 状态,

单条件CASE WHEN 语句

    CASE
        WHEN 采购合同审批表.采购科是否 ='否'  THEN  '终止'
        WHEN 采购合同审批表.部门经理是否 ='否'  THEN  '终止'
        WHEN 采购合同审批表.部门经理是否 ='否'  THEN  '终止'
        WHEN 采购合同审批表.管控部门是否 ='否'  THEN  '终止'
        WHEN 采购合同审批表.律师是否 ='否'  THEN  '终止'
        WHEN 采购合同审批表.财务部是否 ='否'  THEN  '终止'
        WHEN 采购合同审批表.财务总监是否 ='否'  THEN  '终止'
        WHEN 采购合同审批表.总经理是否 ='否'  THEN  '终止'
        WHEN 采购合同审批表.总经理是否 ='是'  THEN  '完成'
        ELSE '审批中'

    END AS 审批状态

复杂CASE WHEN 语句

CASE
            WHEN 总分平均值<=50
            AND 否决提案1+否决提案2+否决提案3+否决提案4+否决提案5+否决提案6+isnull(否决提案7,
            0) > 3
            AND 评委签名1 is not null
            AND 评委签名2 is not null
            AND 评委签名3 is not null
            AND 评委签名4 is not null
            AND 评委签名5 is not null
            AND 评委签名6 is not null     THEN 'D'
            WHEN 总分平均值>50
            AND 总分平均值<=70
            AND 否决提案1+否决提案2+否决提案3+否决提案4+否决提案5+否决提案6+isnull(否决提案7,
            0) > 3
            AND 评委签名1 is not null
            AND 评委签名2 is not null
            AND 评委签名3 is not null
            AND 评委签名4 is not null
            AND 评委签名5 is not null
            AND 评委签名6 is not null     THEN 'C'
            WHEN 总分平均值>70
            AND 总分平均值<=80
            AND 否决提案1+否决提案2+否决提案3+否决提案4+否决提案5+否决提案6 +isnull(否决提案7,
            0)> 3
            AND 评委签名1 is not null
            AND 评委签名2 is not null
            AND 评委签名3 is not null
            AND 评委签名4 is not null
            AND 评委签名5 is not null
            AND 评委签名6 is not null     THEN 'B'
            WHEN 总分平均值>80
            AND 否决提案1+否决提案2+否决提案3+否决提案4+否决提案5+否决提案6 +isnull(否决提案7,
            0)> 3
            AND 评委签名1 is not null
            AND 评委签名2 is not null
            AND 评委签名3 is not null
            AND 评委签名4 is not null
            AND 评委签名5 is not null
            AND 评委签名6 is not null     THEN 'A'
            when 否决提案1+否决提案2+否决提案3+否决提案4+否决提案5+否决提案6+isnull(否决提案7,
            0)  <= 3     then ''
        END AS 奖励等级,
        CASE
            WHEN  评委签名1 is not null
            AND 评委签名2 is not null
            AND 评委签名3 is not null
            AND 评委签名4 is not null
            AND 评委签名5 is not null
            AND 评委签名6 is not null
            AND 否决提案1+否决提案2+否决提案3+否决提案4+否决提案5+否决提案6+isnull(否决提案7,
            0)> 3 THEN '提案通过'
            WHEN  否决提案1+否决提案2+否决提案3+否决提案4+否决提案5+否决提案6+isnull(否决提案7,
            0)  <= 3 THEN '提案否决'
            when 改善专员验证是否 ='否'
            and 改善专员验证 is not null      then '提案否决'
            ELSE '评审中'
        END AS 评审状态