sqlserver CASE WHEN语句示例

最新推荐文章于 2024-09-02 12:26:08 发布

香煎三文鱼

最新推荐文章于 2024-09-02 12:26:08 发布

阅读量1.5k

点赞数

分类专栏： SqlServer 文章标签： case when

本文链接：https://blog.csdn.net/qq_39569480/article/details/105215974

版权

SqlServer 专栏收录该内容

65 篇文章 8 订阅

订阅专栏

简单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 '是' THEN '完成'
WHEN '否' THEN '终止'
ELSE '审批中' END

二、复杂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 评审状态