时间戳

首页 > 解决方案 > PL SQL - If 语句中的 Case When 语句？

if-statement - PL SQL - If 语句中的 Case When 语句？

问题描述

我需要返回一个在 2 个不同列上计算的字段“AMOUNT”。

最初有这个（我知道我可以有 OR 语句，但这样更容易阅读）：

CASE WHEN [condition AND condition] THEN 1
WHEN [condition AND condition] THEN 1
WHEN [condition AND condition] THEN 1
WHEN [condition AND condition] THEN 2
WHEN [condition AND condition] THEN 2
WHEN [condition AND condition] THEN 2
WHEN [condition AND condition] THEN 3
WHEN [condition AND condition] THEN 3
WHEN [condition AND condition] THEN 3
ELSE 4
END AS AMOUNT

但是，条件会根据日期列而变化。所以现在我想在 IF-ELSEIF-ELSE 语句中写一个 CASE WHEN STATMENT

IF (DATE < 01.01.2020) THEN
CASE WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 3
WHEN [condition] THEN 3
WHEN [condition] THEN 3
ELSE 4

ELSEIF (DATE >= 01.01.2020 AND DATE <01.07.2020) THEN
CASE WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 3
WHEN [condition] THEN 3
WHEN [condition] THEN 3
ELSE 4

ELSE
CASE WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 3
WHEN [condition] THEN 3
WHEN [condition] THEN 3
ELSE 4

END AS AMOUNT

它似乎不起作用，所以我认为可能存在语法问题。

标签： if-statementplsqlnestedcase

解决方案

CASE 结构允许嵌套。因此，将 IF...ELSE 替换为外壳：

case when date < date '2020-01-01' then
     case when [condition] then 1
          when [condition] then 1
          when [condition] then 1
          when [condition] then 2
          when [condition] then 2
          when [condition] then 2
          when [condition] then 3
          when [condition] then 3
          when [condition] then 3
          else 4
     end

     when date >= date '2020-01-01' and date < date '2020-01-07'
     case when [condition] then 1
          when [condition] then 1
          when [condition] then 1
          when [condition] then 2
          when [condition] then 2
          when [condition] then 2
          when [condition] then 3
          when [condition] then 3
          when [condition] then 3
          else 4
      end 
    
    else ...  ;

注意：您不应date用作列/变量名称。虽然它不是保留字，但它是数据类型定义。使用数据类型定义作为列/变量名是不好的做法。此外，我将您的隐式日期字符串转换为显式 ISO 标准。ISO 标准不是必需的，但您不应依赖隐式数据转换。

推荐阅读