我要评论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 的数据类型要相同，否则会报错
发表评论