如何在 PostgreSQL 中筛选查询结果

使用 WHERE 的示例

最常见和最直接的检查之一是使用 = 运算符进行相等性检查。在这里，我们检查 customer 表中的每一行是否具有等于 Smith 的 last_name 值

SELECT * FROM customer WHERE last_name = 'Smith';

我们可以向其中添加其他条件，以使用逻辑运算符创建复合表达式。此示例使用 AND 子句添加针对 first_name 列的附加测试。有效行必须满足给定的两个条件

SELECT * FROM customer WHERE first_name = 'John' AND last_name = 'Smith';

同样，我们可以检查是否满足一系列条件中的任何一个。在这里，我们检查 address 表中的行，以查看 zip_code 值是否等于 60626，或者 neighborhood 列是否等于字符串“Roger's Park”。我们使用两个单引号来指示应搜索文字单引号

SELECT * FROM address WHERE zip_code = '60626' OR neighborhood = 'Roger''s Park';

IN 运算符可以像在括号中包装的多个值之间的比较一样工作。如果与任何给定值匹配，则表达式为 TRUE

SELECT * FROM customer WHERE last_name IN ('Smith', 'Johnson', 'Fredrich');

在这里，我们使用 LIKE 检查字符串模式。% 用作通配符，匹配零个或多个字符，因此“Pete”、“Peter”以及任何以“Pete”开头的字符串都将匹配

SELECT * FROM customer WHERE last_name LIKE 'Pete%';

我们可以使用 ~* 运算符进行类似的搜索，以检查使用 POSIX 正则表达式的匹配项，而不考虑大小写。在这种情况下，我们检查 last_name 的值是否以“d”开头并包含子字符串“on”，这将匹配诸如“Dickson”、“Donald”和“Devon”之类的名称

SELECT * FROM customer WHERE last_name ~* '^D.*on.*';

我们可以使用 BETWEEN 和 AND 运算符定义包含范围，来检查街道号码是否在 4000 号地址块内

SELECT * FROM address WHERE street_number BETWEEN 4000 AND 4999;

在这里，我们可以显示任何社会安全号码长度不是 9 位数字的 customer 条目。我们使用 LENGTH() 运算符获取字段中的位数，并使用 <> 检查不相等

SELECT * FROM customer WHERE LENGTH(SSN) <> 9;

使用 GROUP BY 的示例

对于本节中的示例，假设我们有一个名为 pet 的表，我们已定义并填充如下

CREATE TABLE pet (
    id SERIAL PRIMARY KEY,
    type TEXT,
    name TEXT,
    color TEXT,
    age INT
);

INSERT INTO pet (type, name, color, age) VALUES
('dog', 'Spot', 'brown', 3),
('dog', 'Rover', 'black', 7),
('dog', 'Sally', 'brown', 1),
('cat', 'Sabrina', 'black', 8),
('cat', 'Felix', 'white', 4),
('cat', 'Simon', 'orange', 8),
('rabbit', 'Buttons', 'grey', 4),
('rabbit', 'Bunny', 'brown', 8),
('rabbit', 'Briony', 'brown', 6);

GROUP BY 最简单的用法是显示单个列的唯一值范围。为此，请在 SELECT 和 GROUP BY 中使用相同的列。在这里，我们看到了表中使用的所有颜色

SELECT color FROM pet GROUP BY color;

 color
--------
 black
 grey
 brown
 white
 orange
(5 rows)

当您在 SELECT 列列表中超出单列时，您必须将列添加到 GROUP BY 子句，或者使用聚合函数为表示的行组生成单个值。

在这里，我们将 type 添加到 GROUP BY 子句，这意味着每一行将表示 type 和 color 值的唯一组合。我们还添加了 age 列，通过 avg() 函数汇总，以查找每个组的平均年龄

SELECT type, color, avg(age) AS average_age FROM pet GROUP BY type, color;

  type  | color  |     average_age
--------+--------+--------------------
 rabbit | brown  | 7.0000000000000000
 cat    | black  | 8.0000000000000000
 rabbit | grey   | 4.0000000000000000
 dog    | black  | 7.0000000000000000
 dog    | brown  | 2.0000000000000000
 cat    | orange | 8.0000000000000000
 cat    | white  | 4.0000000000000000
(7 rows)

聚合函数在 GROUP BY 子句中使用单列的效果也很好。在这里，我们找到了每种动物类型的平均年龄

SELECT type, avg(age) AS average_age FROM PET GROUP BY type;

  type  |     average_age
--------+--------------------
 rabbit | 6.0000000000000000
 dog    | 3.6666666666666667
 cat    | 6.6666666666666667
(3 rows)

如果我们想显示每种动物类型中最年长的动物，我们可以改为在 age 列上使用 max() 函数。GROUP BY 子句将结果折叠到与以前相同的行中，但是新函数会更改另一列中的结果

SELECT type, max(age) AS oldest FROM pet GROUP BY type;

  type  | oldest
--------+-------
 rabbit |     8
 dog    |     7
 cat    |     8
(3 rows)

使用 HAVING 的示例

使用我们在上一节中介绍的同一张表，我们可以演示 HAVING 子句的工作原理。

在这里，我们按 type 列中唯一的值对 pet 表的行进行分组，并找到 age 的最小值。HAVING 子句然后筛选结果，以删除年龄不大于 1 的任何组

SELECT type, min(age) AS youngest FROM pet GROUP BY type HAVING min(age) > 1;

  type  | youngest
--------+----------
 rabbit |        4
 cat    |        4
(2 rows)

在此示例中，我们按颜色对 pet 中的行进行分组。然后，我们筛选仅表示单行的组。结果向我们显示了每个出现多次的颜色

SELECT color FROM pet GROUP BY color HAVING count(color) > 1;

 color
-------
 black
 brown
(2 rows)

我们可以执行类似的查询来获取只有一种动物的 type 和 color 的组合

SELECT type, color FROM pet GROUP BY type, color HAVING count(color) = 1;

  type  | color
--------+--------
 cat    | black
 rabbit | grey
 dog    | black
 cat    | orange
 cat    | white
(5 rows)

使用 LIMIT 的示例

在本节的示例中，我们将使用前面提到的 pet 表。

如上所述，LIMIT 通常与 ORDER BY 子句结合使用，以显式定义在切片适当数量之前的行排序。在这里，我们根据 pet 条目的 age（从最老到最年轻）对其进行排序。然后，我们使用 LIMIT 显示最老的 5 种动物

SELECT * FROM pet ORDER BY age DESC LIMIT 5;

  type  |  name   | color  | age | id
--------+---------+--------+-----+----
 cat    | Simon   | orange |   8 |  6
 cat    | Sabrina | black  |   8 |  4
 rabbit | Bunny   | brown  |   8 |  8
 dog    | Rover   | black  |   7 |  2
 rabbit | Briany  | brown  |   6 |  9
(5 rows)

如果没有 ORDER BY 子句，LIMIT 将以完全可预测的方式进行选择。返回的结果可能会受到表内条目的顺序或索引的影响。这并不总是一件坏事。

如果我们需要表中任何单个 dog 的记录，我们可以构造如下查询。请记住，虽然结果可能难以预测，但这并不是随机选择，不应如此使用

SELECT * FROM pet WHERE type = 'dog' LIMIT 1;

 type | name | color | age | id
------+------+-------+-----+----
 dog  | Spot | brown |   3 |  1
(1 row)

我们可以使用 OFFSET 子句分页浏览结果。我们包含一个 ORDER BY 子句来定义结果的特定顺序。

对于第一个查询，我们限制结果而不指定 OFFSET，以获取前 3 个最年轻的条目

SELECT * FROM pet ORDER BY age LIMIT 3;

 type | name  | color | age | id
------+-------+-------+-----+----
 dog  | Sally | brown |   1 |  3
 dog  | Spot  | brown |   3 |  1
 cat  | Felix | white |   4 |  5
(3 rows)

要获取接下来的 3 个最年轻的条目，我们可以将 LIMIT 中定义的数字添加到 OFFSET，以跳过我们已经检索到的结果

SELECT * FROM pet ORDER BY age LIMIT 3 OFFSET 3;

  type  |  name   | color | age | id
--------+---------+-------+-----+----
 rabbit | Buttons | grey  |   4 |  7
 rabbit | Briany  | brown |   6 |  9
 dog    | Rover   | black |   7 |  2
(3 rows)

如果我们再次将 LIMIT 添加到 OFFSET，我们将获得接下来的 3 个结果

SELECT * FROM pet ORDER BY age LIMIT 3 OFFSET 6;

  type  |  name   | color  | age | id
--------+---------+--------+-----+----
 cat    | Simon   | orange |   8 |  6
 rabbit | Bunny   | brown  |   8 |  8
 cat    | Sabrina | black  |   8 |  4
(3 rows)

这使我们可以从查询中以可管理的数据块检索行。