已知条件:
①表tb_student
②表tb_sturesult
SELECT * FROM tb_student
SELECT * FROM tb_sturesult
(1)需求:
通过 “tb_student” 表的【id】字段和 “tb_sturesult” 的【stuid】字段做等值连接,查询 “tb_student” 表的【id】、【name】、【sex】、【profession】与 “tb_sturesult” 表的【stuid】、【whichlesson】
SELECT
t1.id ,
t1.name,
t1.sex,
t1.profession,
t2.stuid,
t2.whichlesson
FROM
tb_student AS t1 , tb_sturesult AS t2
WHERE
t1.id = t2.stuId
(2)需求:
要求查询的结果集中多出一列,并且列名称为 “newFiled” ,并且该列的取值为:
①默认取值为对应每一行的【sex】列的值作为”newFiled”列的值
②若存在【name】列值为 ‘无语’ 的行,并且该行的【profession】列和【whichlesson】列的值不相等,则取该行的【name】列的值作为”newFiled”列的值
SELECT
t1.id ,
t1.name,
t1.sex,
t1.profession,
t2.stuid,
t2.whichlesson,
CASE WHEN t1.name='无语' AND t1.profession <> t2.whichlesson THEN t1.name ELSE t1.sex END newFiled
FROM
tb_student AS t1 , tb_sturesult AS t2
WHERE
t1.id = t2.stuId
Case When … Then … ELSE … End… 语句的语法格式为:
Case When 条件表达式 Then 满足条件表示时的取值 Else 不满足条件表达式时的取值 End 新增字段名称
其实它的语法类似于程序代码中的if…else…语句