函数、谓词、CASE 表达式:练习题

本文内容

6.1 对 SQL 谓词 中使用的 Product(商品)表执行如下 2 条 SELECT 语句,能够得到什么样的结果呢?

1
2
3
SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price NOT IN (500, 2800, 5000);

1
2
3
SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

答:

① 的答案

 product_name | purchase_price
--------------+----------------
 打孔器       |            320
 擦菜板       |            790

» 解答

对于 ① 的结果应该没有什么疑问。因为要选取的是进货单价(purchase_price)为 500 日元、2800 日元、5000 日元之外的商品(product_name),所以会得到 320 日元的打孔器和 790 日元的擦菜板两条记录。此外,不仅是 IN,通常的谓词都无法与 NULL 进行比较,因此进货单价(purchase_price)为 NULL 的叉子和圆珠笔都没有出现在结果之中。

② 的答案:无法取出任何记录

 product_name | purchase_price
--------------+----------------

» 解答

② 的结果有必要说明一下。② 的 SQL 仅仅是在 ① 的 NOT IN 的参数中增加了 NULL。并且 ① 的结果中已经排除了进货单价(purchase_price)为 NULL 的记录,因此大家可能会觉得 ② 的结果也是如此。但让人吃惊的是 ② 的 SQL 却无法选取出任何记录。不仅仅是进货单价为 NULL 的记录,连从 ① 中选取出的打孔器和擦菜板也不见了。

其实这是 SQL 中最危险的陷阱。NOT IN 的参数中包含 NULL 时结果通常会为空,也就是无法选取出任何记录。

为什么会得到这样的结果呢?其中的理由十分复杂,属于中级学习的范畴,因此本文中不会详细介绍 1。这里希望大家了解的是 NOT IN 的参数中不能包含 NULL。不仅仅是指定 NULL 的情况,使用子查询作为 NOT IN 的参数时,该子查询的返回值也不能是 NULL。请大家一定要遵守这一规定。

6.2 按照销售单价(sale_price)对练习 6.1 中的 Product(商品)表中的商品进行如下分类。

  • 低档商品 :销售单价在 1000 日元以下(T 恤衫、办公用品、叉子、擦菜板、圆珠笔)

  • 中档商品 :销售单价在 1001 日元以上 3000 日元以下(菜刀)

  • 高档商品 :销售单价在 3001 日元以上(运动 T 恤、高压锅)

请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句,结果如下所示。

执行结果

 low_price | mid_price | high_price
-----------+-----------+----------
         5 |         1 |          2

答:

1
2
3
4
5
6
7
SELECT SUM(CASE WHEN sale_price <= 1000
                THEN 1 ELSE 0 END) AS low_price,
       SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000
                THEN 1 ELSE 0 END) AS mid_price,
       SUM(CASE WHEN sale_price >= 3001
                THEN 1 ELSE 0 END) AS high_price
 FROM Product;

» 解答

大家发现了吗?这与我们在 CASE 表达式 中的“CASE 表达式的书写位置”中学过的使用 CASE 表达式进行行列变换是相似的问题。如果能够使用 CASE 表达式创建出 3 个分类条件的话,之后就可以将其与聚合函数进行组合了。只有计算中间额度商品 2 的条件中的 BETWEEN 需要注意一下。

请参阅#

(完)


  1. 想要了解为什么 NOT IN 会得到这样结果的读者,可以参考《SQL进阶教程》中 1-3 节的内容。 ↩︎

  2. 此处的“中间额度”是笔者创造出来的词语,大家应该能理解其中的含义。 ↩︎

comments powered by Disqus

本文内容