PostgreSQL查询的筛子、排序、聚合、分组？你会用它们搞定数据吗？

一、过滤数据：用WHERE子句精准筛选

查询的核心是“找对数据”，WHERE子句就是PostgreSQL给你的“数据筛子”——它通过条件判断，只保留符合要求的行。

1.1 基本比较与逻辑运算

最基础的过滤用比较运算符（=、<>/!=、>、<、>=、<=）和逻辑运算符（AND、OR、NOT）组合实现。

举个例子（假设我们有一张employees表，包含employee_id、name、department、salary等字段）：

-- 1. 筛选「工程部」的员工
SELECT employee_id, name, department, salary
FROM employees
WHERE department = 'Engineering'; -- 等于判断

-- 2. 筛选「销售部」且「工资>6000」的员工（AND组合条件）
SELECT employee_id, name, department, salary
FROM employees
WHERE department = 'Sales' AND salary > 6000;

-- 3. 筛选「销售部」或「市场部」的员工（OR组合条件）
SELECT employee_id, name, department, salary
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

-- 4. 筛选「非工程部」的员工（NOT取反）
SELECT employee_id, name, department, salary
FROM employees
WHERE NOT department = 'Engineering';

1.2 常用过滤谓词：IN、BETWEEN、LIKE、IS NULL

除了基础比较，PostgreSQL还提供了更灵活的谓词（Predicate），帮你处理复杂场景：

• IN：判断值是否在指定集合中（替代多个OR）

  -- 筛选「销售/市场/工程」三个部门的员工
  SELECT * FROM employees
  WHERE department IN ('Sales', 'Marketing', 'Engineering');
  

• BETWEEN：判断值是否在某个区间内（包含边界）

  -- 筛选工资在5000~8000之间的员工
  SELECT * FROM employees
  WHERE salary BETWEEN 5000 AND 8000;
  

• LIKE：模糊匹配（%代表任意字符，_代表单个字符）

  -- 1. 名字以「J」开头的员工（%匹配后面任意字符）
  SELECT * FROM employees WHERE name LIKE 'J%';
  -- 2. 名字第二个字符是「a」的员工（_匹配单个字符）
  SELECT * FROM employees WHERE name LIKE '_a%';
  

• IS NULL/IS NOT NULL：判断值是否为NULL（注意：NULL不能用=或!=判断）

  -- 筛选「没有上级」的员工（manager_id为NULL）
  SELECT * FROM employees WHERE manager_id IS NULL;
  -- 筛选「有上级」的员工
  SELECT * FROM employees WHERE manager_id IS NOT NULL;
  

1.3 NULL值的特殊处理

NULL代表“未知”或“缺失”，它的逻辑很特殊：

• 任何与NULL的比较（如= NULL、> NULL）结果都是NULL（不是true或false）；
• 聚合函数（如SUM、AVG）会自动忽略NULL。

比如：

-- 错误：无法用=判断NULL（结果为空）
SELECT * FROM employees WHERE manager_id = NULL;
-- 正确：必须用IS NULL
SELECT * FROM employees WHERE manager_id IS NULL;

二、排序数据：用ORDER BY掌控结果顺序

默认情况下，查询结果的顺序是“插入顺序”（不可靠）。ORDER BY子句帮你按指定规则排序，让结果更易读。

2.1 单列排序：升序与降序

• 升序（ASC）：默认规则（从小到大，如数字1→10，字符串A→Z）；
• 降序（DESC）：从大到小（如工资从高到低）。

例子：

-- 按工资降序排序（高薪在前）
SELECT name, salary FROM employees ORDER BY salary DESC;
-- 按入职日期升序排序（老员工在前）
SELECT name, hire_date FROM employees ORDER BY hire_date ASC;

2.2 多列排序：优先级与组合

当单列无法区分顺序时，可以用多列排序——先按第一列排，第一列相同的再按第二列排。

例子：

-- 先按部门升序（A→Z），同一部门内按工资降序（高薪在前）
SELECT name, department, salary FROM employees
ORDER BY department ASC, salary DESC;

2.3 基于计算列或别名排序

你可以用计算结果或列别名排序，不用重复写计算逻辑。

例子：

-- 按「年薪」降序排序（年薪=月薪*12）
SELECT name, salary, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC; -- 用别名排序（更清晰）

-- 也可以直接用计算式排序（但可读性差）
SELECT name, salary, salary * 12 AS annual_salary
FROM employees
ORDER BY salary * 12 DESC;

三、聚合数据：用聚合函数提炼关键信息

聚合函数（Aggregate Function）是“数据 summarizer”——它把多行数据合并成一个结果（如统计总数、计算平均值）。

3.1 常用聚合函数

PostgreSQL提供了5类核心聚合函数：

例子：

-- 1. 统计员工总数
SELECT COUNT(*) AS total_employees FROM employees;

-- 2. 计算「销售部」的总工资
SELECT SUM(salary) AS total_sales_salary
FROM employees WHERE department = 'Sales';

-- 3. 计算「市场部」的平均工资
SELECT AVG(salary) AS avg_marketing_salary
FROM employees WHERE department = 'Marketing';

-- 4. 找最高/最低工资
SELECT MAX(salary) AS max_sal, MIN(salary) AS min_sal FROM employees;

3.2 DISTINCT与聚合：去重统计

DISTINCT可以和聚合函数结合，统计不重复的值。

例子：

-- 统计公司有多少个不同的部门（去重）
SELECT COUNT(DISTINCT department) AS unique_departments FROM employees;

3.3 空值对聚合的影响

所有聚合函数都会自动忽略NULL值。比如：

-- 统计「有上级」的员工数（manager_id不为NULL）
SELECT COUNT(manager_id) AS employees_with_manager FROM employees; 
-- 结果 = 总人数 - manager_id为NULL的人数

四、分组聚合：用GROUP BY与HAVING分组分析

如果想按“类别”聚合（比如“每个部门的平均工资”），需要用GROUP BY子句——它把数据分成多个“组”，每个组单独计算聚合值。

4.1 GROUP BY：按列分组

GROUP BY的规则：SELECT的列要么是分组列，要么是聚合函数（否则PostgreSQL不知道如何处理非分组列的多个值）。

例子：

-- 按部门分组，统计每个部门的员工数和平均工资
SELECT 
  department, -- 分组列
  COUNT(*) AS employee_count, -- 聚合函数
  AVG(salary) AS avg_salary -- 聚合函数
FROM employees
GROUP BY department; -- 按department分组

4.2 HAVING：过滤分组结果

WHERE过滤的是行，HAVING过滤的是分组后的结果（比如“平均工资>6000的部门”）。

例子：

-- 筛选「平均工资>6000」的部门
SELECT 
  department, 
  COUNT(*) AS employee_count, 
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 6000; -- 过滤分组结果

4.3 WHERE与HAVING的区别

比如：

-- 先过滤「工资>4000」的员工，再按部门分组，最后筛选「平均工资>5000」的部门
SELECT 
  department, 
  AVG(salary) AS avg_salary
FROM employees
WHERE salary > 4000 -- 先过滤行
GROUP BY department
HAVING AVG(salary) > 5000; -- 再过滤分组

五、综合实践：组合过滤、排序与聚合

我们用一个真实需求串联所有知识点：

对应的SQL：

SELECT 
  department, -- 分组列
  AVG(salary) AS avg_salary, -- 平均工资（聚合函数）
  COUNT(*) AS employee_count -- 员工数（聚合函数）
FROM employees
WHERE hire_date >= '2020-01-01' -- 过滤2020年后入职的员工（行级过滤）
GROUP BY department -- 按部门分组
HAVING AVG(salary) > 5500 -- 过滤平均工资>5500的部门（分组过滤）
ORDER BY avg_salary DESC; -- 按平均工资降序排序

执行顺序（关键！）：

graph TD
    A[FROM employees] --> B[WHERE hire_date >= '2020-01-01']
    B --> C[GROUP BY department]
    C --> D[HAVING AVG salary > 5500]
    D --> E[SELECT department, AVG salary, COUNT*]
    E --> F[ORDER BY avg_salary DESC]
    F --> G[结果集]

六、课后Quiz：巩固你的查询技能

通过问题强化理解，答案附解析：

问题1

如何筛选出「工资在5000~8000之间」且「属于销售部或市场部」的员工？

答案：

SELECT * FROM employees
WHERE salary BETWEEN 5000 AND 8000
AND department IN ('Sales', 'Marketing');

解析：用BETWEEN处理区间，IN处理多值，AND组合条件。

问题2

如何按部门分组，统计每个部门的「最高工资」和「最低工资」，并且只显示「最高工资>8000」的部门？

答案：

SELECT 
  department, 
  MAX(salary) AS max_sal, 
  MIN(salary) AS min_sal
FROM employees
GROUP BY department
HAVING MAX(salary) > 8000;

解析：GROUP BY分组，MAX/MIN计算极值，HAVING过滤分组结果。

问题3

如何按「入职年份」升序排序，入职年份相同的按「工资降序」排序？（提示：用EXTRACT函数取年份）

答案：

SELECT name, hire_date, salary
FROM employees
ORDER BY 
  EXTRACT(YEAR FROM hire_date) ASC, -- 按入职年份升序
  salary DESC; -- 同一年份按工资降序

解析：EXTRACT(YEAR FROM hire_date)提取入职年份作为排序键，多列排序按顺序优先级。

七、常见报错与解决方法

学习中遇到报错别慌，以下是高频问题的解决方案：

报错1：ERROR: syntax error at or near "WHERE"

原因：WHERE放错位置（比如GROUP BY后用WHERE过滤分组，应该用HAVING）。
错误示例：

SELECT department, AVG(salary)
FROM employees
GROUP BY department
WHERE AVG(salary) > 5000; -- 错误：GROUP BY后不能用WHERE

解决：将WHERE改为HAVING：

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;

报错2：ERROR: column "employees.name" must appear in the GROUP BY clause or be used in an aggregate function

原因：GROUP BY后，SELECT的列不是“分组列”或“聚合函数”（PostgreSQL不知道如何处理非分组列的值）。
错误示例：

SELECT name, department, AVG(salary) -- name不是分组列，也不是聚合函数
FROM employees
GROUP BY department;

解决：

• 方案1：将name加入GROUP BY（按name+department分组）；
• 方案2：用聚合函数（如MAX(name)）；
• 方案3：去掉name列（最常见）。

正确示例（去掉name）：

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

报错3：ERROR: operator does not exist: integer = text

原因：数据类型不匹配（比如用整数和字符串比较）。
错误示例：

SELECT * FROM employees WHERE department = 100; -- department是字符串，100是整数

解决：将整数转为字符串（用单引号）：

SELECT * FROM employees WHERE department = '100';

报错4：ERROR: null value in column "salary" violates not-null constraint

原因：salary列设置了NOT NULL约束，但插入了NULL值。
解决：

• 确保插入的salary非空；
• 若业务允许，修改约束（ALTER TABLE employees ALTER COLUMN salary DROP NOT NULL）。

参考链接

• WHERE子句：www.postgresql.org/docs/17/que…
• ORDER BY子句：www.postgresql.org/docs/17/que…
• 聚合函数：www.postgresql.org/docs/17/fun…
• GROUP BY与HAVING：www.postgresql.org/docs/17/que…

余下文章内容请点击跳转至 个人博客页面 或者 扫码关注或者微信搜一搜：编程智域 前端至全栈交流与成长，阅读完整的文章：PostgreSQL查询的筛子、排序、聚合、分组？你会用它们搞定数据吗？