使用嵌套case更新sql中的查询

4jb9z9bj  于 2021-08-13  发布在  Java
关注(0)|答案(2)|浏览(302)

我想要有一个使用嵌套case的更新查询;

UPDATE mstsales 
SET test = '2017-18'
WHERE salemonth > 3 AND saleyear = 2017 OR salemonth < 4 AND saleyear = 2018  

UPDATE mstsales 
SET test = '2018-19'
WHERE salemonth > 3 AND saleyear = 2018 OR salemonth < 4 AND saleyear = 2019  

UPDATE mstsales 
SET test = '2019-20'
WHERE salemonth > 3 AND saleyear = 2019 OR salemonth < 4 AND saleyear = 2020
5us2dqdw

5us2dqdw1#

你可以做:

UPDATE mstsales 
    SET test = (CASE WHEN salemonth > 3 and saleyear = 2017 or salemonth < 4 and saleyear = 2018 THEN '2017-18'
                     WHEN salemonth > 3 and saleyear = 2018 or salemonth < 4 and saleyear = 2019  THEN '2018-19'
                     WHEN salemonth > 3 and saleyear = 2019 or salemonth < 4 and saleyear = 2020 
                END)
WHERE (salemonth > 3 and saleyear = 2017 or salemonth < 4 and saleyear = 2018) OR 
      (salemonth > 3 and saleyear = 2018 or salemonth < 4 and saleyear = 2019) OR
      (salemonth > 3 and saleyear = 2019 or salemonth < 4 and saleyear = 2020)

我不认为这简化了逻辑。
你没有提到你的数据库。但也有更清洁的替代品:
使用计算列。
使用参考表。
使用派生表进行更新。

ugmeyewa

ugmeyewa2#

希望这有帮助。。

UPDATE mstsales
SET     test =  CASE  
                        WHEN salemonth > 3 and saleyear = 2017 or salemonth < 4 and saleyear = 2018  THEN '2017-18' 
                        WHEN salemonth > 3 and saleyear = 2018 or salemonth < 4 and saleyear = 2019  THEN '2018-19' 
                        WHEN salemonth > 3 and saleyear = 2019 or salemonth < 4 and saleyear = 2020  THEN '2019-20' 
                    END

如果所有情况都有一个共同的条件,那么进入where子句。

相关问题