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类核心聚合函数：

函数	作用	例子
COUNT	统计行数	COUNT(*)（总人数）、COUNT(employee_id)（非空人数）
SUM	求和（仅数值型）	SUM(salary)（总工资）
AVG	求平均值（仅数值型）	AVG(salary)（平均工资）
MIN	求最小值	MIN(salary)（最低工资）
MAX	求最大值	MAX(salary)（最高工资）

例子：

-- 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的区别

特征	WHERE	HAVING
作用对象	行（分组前）	分组（分组后）
可用条件	任意行条件	只能用聚合函数或分组列
执行顺序	先于GROUP BY	后于GROUP BY

比如：

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

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

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

统计每个部门中「2020年以后入职」的员工的平均工资，要求：

1. 平均工资超过5500；
2. 按平均工资降序排序；
3. 显示部门名称、平均工资、员工数。

对应的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查询的筛子、排序、聚合、分组？你会用它们搞定数据吗？

往期文章归档

• PostgreSQL数据类型怎么选才高效不踩坑？ - cmdragon's Blog
• 想解锁PostgreSQL查询从基础到进阶的核心知识点？你都get了吗？ - cmdragon's Blog
• PostgreSQL DELETE居然有这些操作？返回数据、连表删你试过没？ - cmdragon's Blog
• PostgreSQL UPDATE语句怎么玩？从改邮箱到批量更新的避坑技巧你都会吗？ - cmdragon's Blog
  • PostgreSQL插入数据还在逐条敲？批量、冲突处理、返回自增ID的技巧你会吗？ - cmdragon's Blog
  • PostgreSQL的“仓库-房间-货架”游戏，你能建出电商数据库和表吗？ - cmdragon's Blog
  • PostgreSQL 17安装总翻车？Windows/macOS/Linux避坑指南帮你搞定？ - cmdragon's Blog
  • 能当关系型数据库还能玩对象特性，能拆复杂查询还能自动管库存，PostgreSQL凭什么这么香？ - cmdragon's Blog
• 给接口加新字段又不搞崩老客户端？FastAPI的多版本API靠哪三招实现？ - cmdragon's Blog
• 流量突增要搞崩FastAPI？熔断测试是怎么防系统雪崩的？ - cmdragon's Blog
  • FastAPI秒杀库存总变负数？Redis分布式锁能帮你守住底线吗 - cmdragon's Blog
  • FastAPI的CI流水线怎么自动测端点，还能让Allure报告美到犯规？ - cmdragon's Blog
  • 如何用GitHub Actions为FastAPI项目打造自动化测试流水线？ - cmdragon's Blog
  • 如何用Git Hook和CI流水线为FastAPI项目保驾护航？ - cmdragon's Blog
  • FastAPI如何用契约测试确保API的「菜单」与「菜品」一致？
  • 为什么TDD能让你的FastAPI开发飞起来？ - cmdragon's Blog
• 如何用FastAPI玩转多模块测试与异步任务，让代码不再“闹脾气”？ - cmdragon's Blog
• 如何在FastAPI中玩转“时光倒流”的数据库事务回滚测试？
• 如何在FastAPI中优雅地模拟多模块集成测试？ - cmdragon's Blog
• 多环境配置切换机制能否让开发与生产无缝衔接？ - cmdragon's Blog
• 如何在 FastAPI 中巧妙覆盖依赖注入并拦截第三方服务调用？ - cmdragon's Blog
• 为什么你的单元测试需要Mock数据库才能飞起来？ - cmdragon's Blog
• 如何在FastAPI中巧妙隔离依赖项，让单元测试不再头疼？ - cmdragon's Blog
• 如何在FastAPI中巧妙隔离依赖项，让单元测试不再头疼？ - cmdragon's Blog
• 测试覆盖率不够高？这些技巧让你的FastAPI测试无懈可击！ - cmdragon's Blog
• 为什么你的FastAPI测试覆盖率总是低得让人想哭？ - cmdragon's Blog
• 如何让FastAPI测试不再成为你的噩梦？ - cmdragon's Blog
• FastAPI测试环境配置的秘诀，你真的掌握了吗？ - cmdragon's Blog
• 全链路追踪如何让FastAPI微服务架构的每个请求都无所遁形？ - cmdragon's Blog
• 如何在API高并发中玩转资源隔离与限流策略？ - cmdragon's Blog
• 任务分片执行模式如何让你的FastAPI性能飙升？ - cmdragon's Blog
• 冷热任务分离：是提升Web性能的终极秘籍还是技术噱头？ - cmdragon's Blog
• 如何让FastAPI在百万级任务处理中依然游刃有余？ - cmdragon's Blog
• 如何让FastAPI与消息队列的联姻既甜蜜又可靠？ - cmdragon's Blog
• 如何在FastAPI中巧妙实现延迟队列，让任务乖乖等待？ - cmdragon's Blog
• FastAPI的死信队列处理机制：为何你的消息系统需要它？ - cmdragon's Blog
• 如何让FastAPI任务系统在失败时自动告警并自我修复？ - cmdragon's Blog
• 如何用Prometheus和FastAPI打造任务监控的“火眼金睛”？ - cmdragon's Blog
• 如何用APScheduler和FastAPI打造永不宕机的分布式定时任务系统？ - cmdragon's Blog
• 如何在 FastAPI 中玩转 APScheduler，让任务定时自动执行？ - cmdragon's Blog

免费好用的热门在线工具

• 智能提词器 - 应用商店 | By cmdragon
• 魔法简历 - 应用商店 | By cmdragon
• Image Puzzle Tool - 图片拼图工具 | By cmdragon
• 字幕下载工具 - 应用商店 | By cmdragon
• 歌词生成工具 - 应用商店 | By cmdragon
• 网盘资源聚合搜索 - 应用商店 | By cmdragon
• ASCII字符画生成器 - 应用商店 | By cmdragon
• JSON Web Tokens 工具 - 应用商店 | By cmdragon
• Bcrypt 密码工具 - 应用商店 | By cmdragon
• GIF 合成器 - 应用商店 | By cmdragon
• GIF 分解器 - 应用商店 | By cmdragon
• 文本隐写术 - 应用商店 | By cmdragon
• CMDragon 在线工具 - 高级AI工具箱与开发者套件 | 免费好用的在线工具
• 应用商店 - 发现1000+提升效率与开发的AI工具和实用程序 | 免费好用的在线工具
• CMDragon 更新日志 - 最新更新、功能与改进 | 免费好用的在线工具
• 支持我们 - 成为赞助者 | 免费好用的在线工具
• AI文本生成图像 - 应用商店 | 免费好用的在线工具
• 临时邮箱 - 应用商店 | 免费好用的在线工具
• 二维码解析器 - 应用商店 | 免费好用的在线工具
• 文本转思维导图 - 应用商店 | 免费好用的在线工具
• 正则表达式可视化工具 - 应用商店 | 免费好用的在线工具
• 文件隐写工具 - 应用商店 | 免费好用的在线工具
• IPTV 频道探索器 - 应用商店 | 免费好用的在线工具
• 快传 - 应用商店 | 免费好用的在线工具
• 随机抽奖工具 - 应用商店 | 免费好用的在线工具
• 动漫场景查找器 - 应用商店 | 免费好用的在线工具
• 时间工具箱 - 应用商店 | 免费好用的在线工具
• 网速测试 - 应用商店 | 免费好用的在线工具
• AI 智能抠图工具 - 应用商店 | 免费好用的在线工具
• 背景替换工具 - 应用商店 | 免费好用的在线工具
• 艺术二维码生成器 - 应用商店 | 免费好用的在线工具
• Open Graph 元标签生成器 - 应用商店 | 免费好用的在线工具
• 图像对比工具 - 应用商店 | 免费好用的在线工具
• 图片压缩专业版 - 应用商店 | 免费好用的在线工具
• 密码生成器 - 应用商店 | 免费好用的在线工具
• SVG优化器 - 应用商店 | 免费好用的在线工具
• 调色板生成器 - 应用商店 | 免费好用的在线工具
• 在线节拍器 - 应用商店 | 免费好用的在线工具
• IP归属地查询 - 应用商店 | 免费好用的在线工具
• CSS网格布局生成器 - 应用商店 | 免费好用的在线工具
• 邮箱验证工具 - 应用商店 | 免费好用的在线工具
• 书法练习字帖 - 应用商店 | 免费好用的在线工具
• 金融计算器套件 - 应用商店 | 免费好用的在线工具
• 中国亲戚关系计算器 - 应用商店 | 免费好用的在线工具
• Protocol Buffer 工具箱 - 应用商店 | 免费好用的在线工具
• IP归属地查询 - 应用商店 | 免费好用的在线工具
• 图片无损放大 - 应用商店 | 免费好用的在线工具
• 文本比较工具 - 应用商店 | 免费好用的在线工具
• IP批量查询工具 - 应用商店 | 免费好用的在线工具
• 域名查询工具 - 应用商店 | 免费好用的在线工具
• DNS工具箱 - 应用商店 | 免费好用的在线工具
• 网站图标生成器 - 应用商店 | 免费好用的在线工具
• XML Sitemap