sql 转置_SQL基础 CASE WHEN

SQL中的if else 表达式：

CASE WHEN id = 1 THEN 111
WHEN id = 2 THEN 222
ELSE 333
END

等效于python中的

if id == 1: 1111
elif id == 2: 222
else: 333

 INSERT INTO Stu VALUES(8,'GI',3);
 INSERT INTO Stu VALUES(9,'Ge',4);

将1班学生转到2班，2班学生转到1班，其他班级学生不变

SELECT id, name, 
CASE WHEN classId = 1 THEN 2
CASE WHEN classId = 2 THEN 1
ELSE classId END
classId FROM Stu;

将表转置并统计：

CREATE TABLE grade{
  name varchar,
  subject varchar,
  score  numeric
};
INSERT INTO VALUES('A','JAVA',1);
INSERT INTO VALUES('B','JAVA',2);
INSERT INTO VALUES('C','JAVA',3);
INSERT INTO VALUES('A','Python',11);
INSERT INTO VALUES('B','Python',21);
INSERT INTO VALUES('C','Python',31);
INSERT INTO VALUES('A','SQL',41);
INSERT INTO VALUES('B','SQL',42);
INSERT INTO VALUES('C','SQL',43);

显示ABC的各科成绩

SELECT name,
max(CASE WHEN subject = 'JAVA' THEN score ELSE NULL ) as JAVA, 
max(CASE WHEN subject = 'Python' THEN score ELSE NULL) as Python, 
max(CASE WHEN subject = 'SQL' THEN score ELSE NULL ) as SQL
FROM Grade
GROUP BY name