SQL中的case when then else end用法（数据库内容纵向变横向）

SQL中的case when then else end用法

当我们需要从数据源上 直接判断数据显示代表的含义的时候 ,就可以在SQL语句中使用 Case When这个函数了.

Case具有两种格式。简单Case函数和Case搜索函数。
简单函数

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

搜索函数

CASE WHEN [expr] THEN [result1]…ELSE [default] END

 

达梦数据库建表SQL

CREATE TABLE "TEST"."CJT"
(
"ID" INT NOT NULL,
"KC" VARCHAR(50),
"FS" INT,
"NAME" VARCHAR(50),
CLUSTER PRIMARY KEY("ID")) STORAGE(ON "TEST", CLUSTERBTR) ;

COMMENT ON TABLE "TEST"."CJT" IS '成绩表';
COMMENT ON COLUMN "TEST"."CJT"."ID" IS '主键';
COMMENT ON COLUMN "TEST"."CJT"."KC" IS '课程名称';
COMMENT ON COLUMN "TEST"."CJT"."FS" IS '分数';
COMMENT ON COLUMN "TEST"."CJT"."NAME" IS '姓名';

 

 

 

 

执行SQL

SELECT 
NAME AS "姓名",
(CASE KC WHEN '语文' THEN FS ELSE 0 END) AS "语文",
(CASE KC WHEN '数学' THEN FS ELSE 0 END) AS "数学",
(CASE KC WHEN '英语' THEN FS ELSE 0 END) AS "英语"
FROM TEST.CJT

 

 

执行SQL

SELECT 
NAME AS "姓名",
MAX(CASE KC WHEN '语文' THEN FS ELSE 0 END) AS "语文",
MAX(CASE KC WHEN '数学' THEN FS ELSE 0 END) AS "数学",
MAX(CASE KC WHEN '英语' THEN FS ELSE 0 END) AS "英语"
FROM TEST.CJT GROUP BY CJT.NAME;