PostgreSQL 查询详解

SELECT 语句是 SQL 最核心的操作，本章将详细介绍 PostgreSQL 中的各种查询技术。

基础查询

SELECT 语法结构

SELECT [DISTINCT] columns [AS alias]
FROM table_name
[JOIN ...]
[WHERE conditions]
[GROUP BY columns]
[HAVING conditions]
[ORDER BY columns [ASC|DESC]]
[LIMIT number] [OFFSET number];

基本查询示例

首先创建测试数据：

-- 创建示例表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    hire_date DATE,
    status VARCHAR(20) DEFAULT 'active'
);

-- 插入测试数据
INSERT INTO employees (name, department, salary, hire_date, status) VALUES
('张三', '技术部', 15000, '2020-01-15', 'active'),
('李四', '技术部', 12000, '2021-03-20', 'active'),
('王五', '市场部', 10000, '2019-06-10', 'active'),
('赵六', '市场部', 11000, '2022-02-05', 'inactive'),
('孙七', '财务部', 13000, '2020-08-01', 'active'),
('周八', '财务部', 9000, '2023-01-10', 'active');

查询所有列

-- 查询所有数据
SELECT * FROM employees;

-- 查询指定列
SELECT name, department, salary FROM employees;

使用表达式

-- 计算字段
SELECT 
    name,
    salary,
    salary * 12 AS annual_salary
FROM employees;

-- 使用函数
SELECT 
    name,
    UPPER(name) AS name_upper,
    LOWER(department) AS dept_lower
FROM employees;

-- 条件表达式
SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 12000 THEN '高收入'
        WHEN salary >= 10000 THEN '中等收入'
        ELSE '低收入'
    END AS salary_level
FROM employees;

DISTINCT 去重

-- 查询所有部门（去重）
SELECT DISTINCT department FROM employees;

-- 多列去重
SELECT DISTINCT department, status FROM employees;

-- 使用 DISTINCT ON（PostgreSQL 特有）
SELECT DISTINCT ON (department) * FROM employees
ORDER BY department, salary DESC;

WHERE 条件查询

基础条件

-- 等于
SELECT * FROM employees WHERE department = '技术部';

-- 不等于
SELECT * FROM employees WHERE department != '财务部';

-- 大于、小于
SELECT * FROM employees WHERE salary > 10000;
SELECT * FROM employees WHERE hire_date < '2021-01-01';

-- 大于等于、小于等于
SELECT * FROM employees WHERE salary >= 10000;
SELECT * FROM employees WHERE salary <= 15000;

组合条件

-- AND 条件
SELECT * FROM employees 
WHERE department = '技术部' AND salary > 10000;

-- OR 条件
SELECT * FROM employees 
WHERE department = '技术部' OR department = '市场部';

-- NOT 条件
SELECT * FROM employees WHERE NOT status = 'active';

-- 复杂组合
SELECT * FROM employees 
WHERE (department = '技术部' OR department = '市场部')
  AND salary >= 10000
  AND status = 'active';

IN 和 BETWEEN

-- IN：多个值匹配
SELECT * FROM employees 
WHERE department IN ('技术部', '市场部', '财务部');

-- NOT IN
SELECT * FROM employees 
WHERE department NOT IN ('技术部');

-- BETWEEN：范围查询（包含边界）
SELECT * FROM employees 
WHERE salary BETWEEN 10000 AND 15000;

-- NOT BETWEEN
SELECT * FROM employees 
WHERE salary NOT BETWEEN 10000 AND 15000;

LIKE 和 ILIKE

-- LIKE：区分大小写的模式匹配
-- %：任意字符（0个或多个）
-- _：任意单个字符

-- 以"张"开头
SELECT * FROM employees WHERE name LIKE '张%';

-- 以"张"开头且名字只有2个字
SELECT * FROM employees WHERE name LIKE '张_';

-- 包含"三"
SELECT * FROM employees WHERE name LIKE '%三%';

-- 不以"张"开头
SELECT * FROM employees WHERE name NOT LIKE '张%';

-- ILIKE：不区分大小写
SELECT * FROM employees WHERE name ILIKE '%ZHANG%';

NULL 处理

-- 创建带 NULL 的测试表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    price NUMERIC(10, 2),
    discount NUMERIC(10, 2)
);

INSERT INTO products (name, price, discount) VALUES
('A', 100, 10),
('B', 200, NULL),
('C', NULL, 20);

-- IS NULL：查找 NULL 值
SELECT * FROM products WHERE discount IS NULL;

-- IS NOT NULL：查找非 NULL 值
SELECT * FROM products WHERE discount IS NOT NULL;

-- COALESCE：返回第一个非 NULL 值
SELECT name, COALESCE(price, 0) AS price FROM products;
SELECT name, COALESCE(price, 0) - COALESCE(discount, 0) AS final_price FROM products;

-- NULLIF：返回 NULL 如果两个值相等
SELECT NULLIF(10, 10);  -- NULL
SELECT NULLIF(10, 20);  -- 10

ORDER BY 排序

-- 升序（默认）
SELECT * FROM employees ORDER BY salary;

-- 降序
SELECT * FROM employees ORDER BY salary DESC;

-- 多列排序
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

-- 按表达式排序
SELECT * FROM employees 
ORDER BY LENGTH(name);

-- 按列位置排序（第2列）
SELECT * FROM employees ORDER BY 2;

-- NULL 值排序位置
SELECT * FROM employees ORDER BY discount NULLS LAST;
SELECT * FROM employees ORDER BY discount NULLS FIRST;

LIMIT 和 OFFSET 分页

-- LIMIT：限制返回行数
SELECT * FROM employees LIMIT 3;

-- OFFSET：跳过前 N 行
SELECT * FROM employees OFFSET 3;

-- 分页查询
SELECT * FROM employees 
ORDER BY id
LIMIT 10 OFFSET 0;   -- 第1页

SELECT * FROM employees 
ORDER BY id
LIMIT 10 OFFSET 10;  -- 第2页

-- PostgreSQL 特有：FETCH（兼容 SQL 标准）
SELECT * FROM employees 
ORDER BY id
FETCH FIRST 10 ROWS ONLY;

-- 获取随机行
SELECT * FROM employees ORDER BY RANDOM() LIMIT 1;

GROUP BY 分组

基本分组

-- 按部门分组
SELECT department FROM employees GROUP BY department;

-- 分组 + 聚合
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department;

HAVING 过滤分组

-- WHERE 和 HAVING 的区别
-- WHERE：过滤行（在分组前）
-- HAVING：过滤分组（在分组后）

-- 统计各部门人数，只显示人数大于1的部门
SELECT 
    department,
    COUNT(*) AS count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

-- 统计各部门平均工资，只显示平均工资大于10000的
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING AVG(salary) > 10000
ORDER BY avg_salary DESC;

GROUP BY 扩展

-- GROUP BY ROLLUP：生成小计和总计
SELECT 
    COALESCE(department, '总计') AS department,
    COUNT(*) AS count,
    SUM(salary) AS total
FROM employees
GROUP BY ROLLUP(department);

-- GROUP BY CUBE：生成所有组合的小计
SELECT 
    department,
    status,
    COUNT(*) AS count
FROM employees
GROUP BY CUBE(department, status);

-- GROUPING SETS：指定分组集合
SELECT 
    department,
    status,
    COUNT(*) AS count
FROM employees
GROUP BY GROUPING SETS (
    (department),      -- 按部门
    (status),           -- 按状态
    ()                 -- 总计
);

-- 使用 GROUPING 判断是否是小计行
SELECT 
    GROUPING(department) AS is_dept_subtotal,
    department,
    COUNT(*) AS count
FROM employees
GROUP BY ROLLUP(department);

聚合函数

常用聚合函数

-- COUNT：计数
SELECT COUNT(*) FROM employees;           -- 总行数
SELECT COUNT(discount) FROM products;    -- 非 NULL 行数
SELECT COUNT(DISTINCT department) FROM employees;  -- 不同值数量

-- SUM：求和
SELECT SUM(salary) FROM employees;

-- AVG：平均值
SELECT AVG(salary) FROM employees;

-- MAX/MIN：最大值/最小值
SELECT MAX(salary), MIN(salary) FROM employees;

-- 字符串聚合
SELECT STRING_AGG(name, ',') FROM employees;
SELECT STRING_AGG(name, ',' ORDER BY salary DESC) FROM employees;
SELECT STRING_AGG(DISTINCT department, ',') FROM employees;

数组聚合

-- ARRAY_AGG：聚合为数组
SELECT ARRAY_AGG(name) FROM employees;
SELECT ARRAY_AGG(name ORDER BY salary DESC) FROM employees;

-- JSON_AGG：聚合为 JSON 数组
SELECT JSON_AGG(name) FROM employees;
SELECT JSON_AGG(*) FROM employees;

-- JSONB_AGG：聚合为 JSONB 数组
SELECT JSONB_AGG(name) FROM employees;

-- JSON_OBJECT_AGG：聚合为 JSON 对象
SELECT JSON_OBJECT_AGG(name, salary) FROM employees;

窗口聚合

-- 窗口函数：在不分组的情况下计算聚合
SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER () AS avg_salary,           -- 全部平均
    SUM(salary) OVER (PARTITION BY department) AS dept_total  -- 部门合计
FROM employees;

-- 排名函数
SELECT 
    name,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank,         -- 跳跃排名
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,  -- 连续排名
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num  -- 行号
FROM employees;

-- 前后值
SELECT 
    name,
    salary,
    LAG(salary) OVER (ORDER BY salary DESC) AS prev_salary,
    LEAD(salary) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;

-- 首尾值
SELECT 
    name,
    salary,
    FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS highest,
    LAST_VALUE(salary) OVER (ORDER BY salary DESC) AS lowest
FROM employees;

-- 百分位数
SELECT 
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_disc
FROM employees
GROUP BY department;

CASE 表达式

简单 CASE

SELECT 
    name,
    department,
    CASE department
        WHEN '技术部' THEN '研发'
        WHEN '市场部' THEN '营销'
        WHEN '财务部' THEN '财务'
        ELSE '其他'
    END AS dept_cn
FROM employees;

搜索 CASE

SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 15000 THEN '高薪'
        WHEN salary >= 12000 THEN '中等'
        WHEN salary >= 10000 THEN '一般'
        ELSE '偏低'
    END AS salary_level
FROM employees;

CASE 在聚合中应用

-- 统计各部门男女比例
SELECT 
    department,
    COUNT(*) FILTER (WHERE gender = '男') AS male_count,
    COUNT(*) FILTER (WHERE gender = '女') AS female_count,
    COUNT(*) FILTER (WHERE gender = '其他') AS other_count
FROM employees
GROUP BY department;

-- 使用 SUM 替代 COUNT...FILTER
SELECT 
    SUM(CASE WHEN gender = '男' THEN 1 ELSE 0 END) AS male_count
FROM employees;

联合查询

UNION 和 UNION ALL

-- UNION：合并并去重
SELECT name, salary FROM employees WHERE department = '技术部'
UNION
SELECT name, salary FROM employees WHERE department = '市场部';

-- UNION ALL：合并但不去重（更快）
SELECT name, salary FROM employees WHERE department = '技术部'
UNION ALL
SELECT name, salary FROM employees WHERE department = '市场部';

-- UNION 排序
SELECT name, salary FROM employees WHERE department = '技术部'
UNION
SELECT name, salary FROM employees WHERE department = '市场部'
ORDER BY salary DESC;

INTERSECT 和 EXCEPT

-- INTERSECT：交集
SELECT name FROM employees WHERE salary > 10000
INTERSECT
SELECT name FROM employees WHERE department = '技术部';

-- EXCEPT：差集
SELECT name FROM employees
EXCEPT
SELECT name FROM employees WHERE status = 'inactive';

小结

本章我们学习了 PostgreSQL 查询的各个方面：

1. 基础查询：SELECT、列选择、表达式计算
2. 条件查询：WHERE、LIKE、NULL 处理
3. 排序：ORDER BY、多列排序、NULL 位置
4. 分页：LIMIT、OFFSET
5. 分组：GROUP BY、HAVING、ROLLUP、CUBE
6. 聚合函数：COUNT、SUM、AVG、MAX、MIN、字符串聚合
7. 窗口函数：排名、LAG/LEAD、FIRST/LAST_VALUE、PERCENTILE
8. CASE 表达式：条件转换
9. 联合查询：UNION、INTERSECT、EXCEPT

练习

1. 查询所有工资在 10000-15000 之间的员工
2. 按部门统计平均工资，只显示平均工资最高的部门
3. 查询每个部门的工资前三名
4. 计算每个员工与所在部门平均工资的差距
5. 统计各部门不同状态员工的数量