MySQL case when 使用

女爷i 2022-03-08 13:08 253阅读 0赞

# [MySQL case when 使用][MySQL case when] #

case when 自定义排序时的使用  
根据 case when 新的 sort字段排序

case 
        when t2.status = 4
        and t2.expire_time>UNIX_TIMESTAMP()
        and t2.expire_time<UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 60 day)) then 4
            when `status` = 2  then 3
            when `status` = 3 then 2
            when t2.status = 4
            and t2.expire_time>UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 60 day))
            and t2.expire_time<UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 1 year)) then 1
        else 0
        end sort     
        
         $query->orderBy('sort desc ,t2.expire_time desc,t2.created_at desc');

*  当colume 与condition 条件相等时结果为result

case colume 
        when condition then result
        when condition then result
        when condition then result
    else result
    end

![image][]

*  当满足某一条件时，执行某一result

case  
        when condition then result
        when condition then result
        when condition then result
    else result
    end

![image][image 1]

*  当满足某一条件时，执行某一result,把该结果赋值到new\_column\_name 字段中

case  
        when condition then result
        when condition then result
        when condition then result
    else result
    end new_column_name

case when 用在select 语句中，新的字段new\_column\_name可以用来排序，但是不能用在where中

![image][image 2]  
![image][image 3]  
end 后面加as new\_colume\_name  
![image][image 4]

[MySQL case when]: https://www.cnblogs.com/xp796/p/8042100.html
[image]: https://note.youdao.com/yws/res/40014/WEBRESOURCE9b81865bae91de8981d01aea2d02ff80
[image 1]: https://note.youdao.com/yws/res/40025/WEBRESOURCE6489287fb0198b71decf1df9891ccac4
[image 2]: https://note.youdao.com/yws/res/40005/WEBRESOURCE11723168c67827421488edc431994df6
[image 3]: https://note.youdao.com/yws/res/40008/WEBRESOURCE18e4a75a260351d045d312c49f4c9d84
[image 4]: https://note.youdao.com/yws/res/40031/WEBRESOURCE062686f5358c23e49e0030ddf32bc9bb