coalesce()函数和 using case

COALESCE()函数

1. 函数解释

• 命令格式：coalesce(expr 1,expr 2,…)；

• 用途：返回列表中第一个NULL 的值，如果列表中所有的值都是NULL则返回NULL；

• 参数说明：expr i 是要测试的值。所有这些值类型必须相同或者为NULL，否则会引发异常。

• 返回值：返回值类型和参数类型相同

2. 示例

题目：Use COALESCE to print the mobile number. Use the number ‘07986 444 2266’ if there is no number given. Show teacher name and mobile number or ‘07986 444 2266’

SELECT name,COALESCE(mobile,'07986 444 2266')
FROM teacher

结果：

Using CASE

示例

• SQLZOO Using Null的第9题

Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2 and ‘Art’ otherwise.

SELECT name,
CASE WHEN dept = '1' OR dept = '2' then 'Sci'
ELSE 'Art'
END
FROM teacher

结果：

• SQLZOO Using Null的第10题
  Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2, show ‘Art’ if the teacher’s dept is 3 and ‘None’ otherwise.

SELECT name,
CASE WHEN dept = '1' OR dept = '2' then 'Sci'
WHEN dept = '3' then 'Art'
ELSE 'None'
END
FROM teacher

结果：