Simplify nested case when statement

问题:

Below is my current SELECT CASE statement:

SELECT CASE 
WHEN edition = 'STAN' AND has9 = 1 THEN '9'
WHEN edition = 'STAN' AND has8 = 1 THEN '8'
WHEN edition = 'STAN' AND has7 = 1 THEN '7' 
WHEN edition = 'STAN' AND hasOLD = 1 THEN 'OLD'
WHEN edition = 'SUI'  AND has_s9 = 1 THEN 'S9' 
WHEN edition = 'SUI'  AND has_s8 = 1 THEN 'S8' ELSE 'S7' END AS version

I do not always want to repeat the edition = 'xxx' condition, such as

CASE WHEN edition = 'STAN' AND has9 = 1 THEN '9' ELSE WHEN has8 = 1 THEN '8' ELSE WHEN has7 = '7' ELSE WHEN edition 'SUI' AND has_s9 = 1 THEN 'S9' ELSE ...

In Excel this is fairly easy but how can I compile that in PostgreSQL?

回答1:

Try this

SELECT CASE 
WHEN edition = 'STAN' THEN 
     CASE 
          WHEN has9 = 1 THEN '9'
          WHEN has8 = 1 THEN '8'
          WHEN has7 = 1 THEN '7'
          WHEN hasOLD = 1 THEN 'OLD'
     END
WHEN edition = 'SUI' THEN
     CASE 
          WHEN has9 = 1 THEN 'S9'
          WHEN has8 = 1 THEN 'S8'
     END
ELSE 'S7' END AS version

回答2:

You can nest your case when.

By the way, when you make a case on a single field, you can do

case <field> when <value>
             when <otherValue>

rather then

 case when <field> = <value>
      when <field> = <otherValue>

So

case edition
      when 'STAN'
         case when has9 = 1 then '9'
              when has8 = 1 then '8'
              when has7 = 1 then '7'
              when hasOLD = 1 then 'OLD'
         end
     when 'SUI'
         case when has_s9 = 1 then 'S9'
              when has_s8 = 1 then 'S8'
         end
     else 'S7'
end as version

回答3:

Postgres supports both syntax variants for CASE: the "simple CASE" and the "searched CASE". Use a "simple CASE". And you can also nest to mix both variants:

SELECT CASE edition 
       WHEN 'STAN' THEN 
          CASE WHEN has9 = 1 THEN '9'
               WHEN has8 = 1 THEN '8'
               WHEN has7 = 1 THEN '7' 
               WHEN hasOLD = 1 THEN 'OLD'
               -- no ELSE means ELSE NULL
          END
       WHEN 'SUI' THEN
          CASE WHEN has_s9 = 1 THEN 'S9' 
               WHEN has_s8 = 1 THEN 'S8'
          END  -- no ELSE means ELSE NULL
       ELSE 'S7'
       END AS version;

To carry this one step further , you can switch constant and variable. Both are just expressions and can trade places in Postgres. Maybe not as easy to read and understand, but if you want the shortest code ...

SELECT CASE edition 
       WHEN 'STAN' THEN 
          CASE 1
          WHEN has9   THEN '9'
          WHEN has8   THEN '8'
          WHEN has7   THEN '7' 
          WHEN hasOLD THEN 'OLD'
          END
       WHEN 'SUI' THEN
          CASE 1
          WHEN has_s9 THEN 'S9' 
          WHEN has_s8 THEN 'S8'
          END
       ELSE 'S7'
       END AS version;

Aside: The syntax for CASE statements in plpgsql (the procedural language) is slightly different. (Different thing, really!)