过滤与表达式

目标

将 BETWEEN、IN、LIKE、COALESCE、CASE、IS DISTINCT FROM、LIMIT/OFFSET 应用于真实数据，准确描述所需集合。

为什么重要

在 SQL 中编写条件就是定义集合，而这种集合运算中存在第三种真值。涉及 NULL 的比较既不为真也不为假，而是未知；WHERE 只让为真的行通过，因此未知值会悄悄被过滤。

正因如此，city = '서울' 与 city <> '서울' 的行数相加并不等于总数，因为 NULL 行不属于任何一边。这类错误不会报错，只会悄悄产生错误数字。亲自验证后，日后报表数字不符时便知道应先怀疑哪里。

步骤

1. 创建包含 price 不低于 50000 且不高于 150000 的商品的视图 v_mid_price。
2. 创建包含 status 为 paid 或 shipped 的订单的视图 v_multi_status。
3. 创建包含 name 中含有 프로 的商品的视图 v_pro_products。
4. 只保留客户的 id 和 city 两列，把空的 city 替换为 미상，并创建视图 v_city_filled。列名为 id、city。
5. 保存订单的 id 和规模分类，创建视图 v_order_size。列为 id、size；total_amount 不低于 1000000 时为 대형，不低于 300000 时为 중형，其余为 소형。
6. 创建包含 city 不是 서울 的客户的视图 v_not_seoul。city 为空的客户也必须包含在内。
7. 商品按 price 降序、相同价格按 id 升序排列时，创建包含第 3 页（每页 20 条）的视图 v_page3。
8. 针对 country 为 KR、is_active 为真、signup_date 不早于 2024-07-01、tier 为 gold 或 vip 的客户，创建保存其 id、name、tier、signup_date 的视图 v_target。按 signup_date 降序排列，相同时按 id 升序。

参考

• 连接：PGPASSWORD=lab psql -h 127.0.0.1 -U lab -d labdb
• 评分会检查视图列名和顺序，请准确设置别名。
• 常见错误 1：BETWEEN 包含两个端点。
• 常见错误 2：第 6 步写成 city <> '서울' 会漏掉 city 为 NULL 的客户。

按价格区间筛选

创建包含 price 不低于 50000 且不高于 150000 的商品的视图 v_mid_price。

有一个关键字可一次表达区间条件。请确认两个端点是否包含在内。

从多个值中选择一个

创建包含 status 为 paid 或 shipped 的订单的视图 v_multi_status。

有一个关键字可用列表表达条件，无需重复使用多个 OR。

查找名称含特定字符串的商品

创建包含 name 中含有 프로 的商品的视图 v_pro_products。

查找部分匹配时，需要在前后都添加通配符。

用默认值填充空值

只保留客户的 id 和 city 两列，把空的 city 替换为 미상，并创建视图 v_city_filled。列名为 id、city。

有一个函数只在值为 NULL 时返回替代值。原本有值的行必须保持不变。

根据条件分类

保存订单的 id 和规模分类，创建视图 v_order_size。列为 id、size；total_amount 不低于 1000000 时为 대형，不低于 300000 时为 중형，其余为 소형。

CASE WHEN 从上到下选择第一个为真的分支。从最大区间开始写可以避免重叠。

连同 NULL 一起取反

创建包含 city 不是 서울 的客户的视图 v_not_seoul。city 为空的客户也必须包含在内。

普通不等运算会过滤掉 NULL 行。有一个运算符可以把 NULL 当成一个值来比较。

获取第 3 页

商品按 price 降序、相同价格按 id 升序排列时，创建包含第 3 页（每页 20 条）的视图 v_page3。

分别指定跳过的行数和获取的行数。每页 20 条时，第 3 页应跳过多少条？

组合四个条件的目标列表

针对 country 为 KR、is_active 为真、signup_date 不早于 2024-07-01、tier 为 gold 或 vip 的客户，创建保存其 id、name、tier、signup_date 的视图 v_target。按 signup_date 降序排列，相同时按 id 升序。

用 AND 连接之前使用过的条件，按顺序选择所需列，并指定两个排序条件。