sql - 从表中选择时如何解决 NULL 值问题？

Question

我有4 3 列的学生表：

1. 第一个有名字的
2. middle具有中间名和 NULL VALUES
3. 姓_
4. 房子 房子的名字
5. 出生每个人的出生日期

我想选择所有记录，其中房子是Gryffindor，包括中间值为 NULL 的记录。

SELECT first || " " || middle || " " || last || "," || " " || "born" || " " || birth AS birth
FROM students
WHERE house LIKE "Gryffindor"
ORDER BY last, first;

标签： sqldatabase

Answer 1

这里一种快速而肮脏的解决方法是COALESCE对所有名称组件使用：

SELECT COALESCE(first, '') || ' ' ||
       COALESCE(middle || ' ', '') ||
       COALESCE(last, '') || ', born ' || birth AS birth
FROM students
WHERE house = 'Gryffindor'  -- assuming you want a specific house
ORDER BY last, first;