![8a603d3bdc67f6b901a9c23c415e1d28.png](https://i-blog.csdnimg.cn/blog_migrate/977f0c4143ab337e8441fcec75dfd5b8.jpeg)
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