PostgreSQL的case when语句使用

case when语句第一种方式：

case 
    when 表达式1 then 结果1
    when 表达式2 then 结果2
    else 结果n
end
举例1：
select sum(
        case
            when rental_rate=0.99 then 1
            else 0
        end
    ) as "aa",
    
    sum(
     case
        when rental_rate=2.99 then 1
        else 0
     end
    ) as "bb",
    
    sum(
        case
            when rental_rate=4.99 then 1
            else 0
        end
    ) as "cc"
    
    from film;
结果：
aa     bb     cc
341    323    336


【注】：as后接的别名需要带双引号，否则报语法错误


case when语句第二种方式：

case 表达式
    when 匹配1 then 结果1
    when 匹配2 then 结果2
    else 结果n
end
举例2：
    select sum(
        case rental_rate
            when 0.99 then 1
            else 0
        end
    ) as "aa",
    
    sum(
        case rental_rate
            when 2.99 then 1
            else 0
        end
    ) as "bb",
    
    sum(
        case rental_rate
            when 4.99 then 1
            else 0
        end
    ) as "cc"
    from film;
结果：
aa     bb     cc
341    323    336

上面是在网上搜到的介绍

下面是自己实际用到的情况，供自己学习记录。

SELECT  c.unit,
		case when d.money ::DECIMAL = 0.00
		then '0%'
		   else concat(round(c.number ::DECIMAL/d.money ::DECIMAL*100,2),'%')  end as rate  
FROM (
SELECT p.unit,sum(p.number::DECIMAL) as number FROM table p 
WHERE  p.code IN (SELECT  code FROM code_table ) 
GROUP BY  p.unit
) c
left join (
SELECT  unit,sum(money::DECIMAL) as money FROM code_table 
GROUP BY unit
) d
on  c.unit = d.unit 

使用时case when then 的数据类型要相同，否则会报错