SQL中的case when then else end用法

最新推荐文章于 2023-12-27 14:00:42 发布

原创最新推荐文章于 2023-12-27 14:00:42 发布 · 2.6k 阅读

6 ·

本内容遵循CC 4.0 BY-SA版权协议

本文介绍SQL中使用case when then else end函数进行复杂条件查询的方法，包括性别人口统计和学生各科成绩的最大值统计。

一种选择性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"