SQL中的case when then else end用法

最新推荐文章于 2024-08-12 22:14:07 发布

猿小强

最新推荐文章于 2024-08-12 22:14:07 发布

阅读量2.3k

点赞数 3

本文链接：https://blog.csdn.net/sinat_35814207/article/details/80901982

版权

一种选择性sql函数：case when 是条件 then 是满足条件后的结果，当结果为null的时候，用else 后的0填充，end表示此函数结束。

SELECT "country",
	"SUM"(CASE "sex" WHEN 1 THEN "population" ELSE 0 END) 男,
	"SUM"(CASE "sex" WHEN 2 THEN "population" ELSE 0 END) 女
FROM "test12" GROUP BY "country"
<==> 等价于
SELECT "country",
  SUM( CASE WHEN "sex" = '1' THEN "population" ELSE 0 END) AS 男,  
  SUM( CASE WHEN "sex" = '2' THEN "population" ELSE 0 END) AS 女 
FROM  "test12" GROUP BY "country";

SELECT DISTINCT"test11"."id",AA."name",AA."数学",AA."英语",AA."语文" FROM(
select "name",
  max(case "kecheng" when '语文' then "score" else 0 end) 语文,
  max(case "kecheng" when '数学' then "score" else 0 end) 数学,
  max(case "kecheng" when '英语' then "score" else 0 end) 英语
from "test11"
GROUP BY "name"
) aa
INNER JOIN "test11" ON AA."name" = "test11"."name" ORDER BY "id"