选择列表

选择列表（Select List）是 PostgreSQL 查询中的核心组件，它决定了查询结果中包含哪些列。本文将详细介绍选择列表的各种用法、特性和最佳实践。

概述

在 PostgreSQL 的 SELECT 查询中，表表达式（FROM 子句）构建出一个中间虚拟表，这个表可能经过了表连接、行过滤、分组等操作。选择列表的作用就是从这个中间表中选择需要输出的列。

选择列表项

基本用法

1. 选择所有列（*）

最简单的选择列表是使用星号（*），它会输出表表达式产生的所有列：

sql

-- 选择 employees 表的所有列
SELECT * FROM employees;

提示使用 * 虽然方便，但在生产环境中应谨慎使用，因为：

可能返回不需要的数据，浪费网络带宽
当表结构变化时，应用程序可能受到影响
影响查询性能，特别是当表有很多列时

2. 选择指定列

更常见的做法是明确指定需要的列：

sql

-- 选择员工的姓名和薪资
SELECT first_name, last_name, salary FROM employees;

示例演示：

假设有以下 employees 表：

employee_id	first_name	last_name	salary	department	hire_date
1	张	三	5000	IT	2023-01-15
2	李	四	6000	HR	2022-03-20
3	王	五	5500	IT	2023-05-10

sql

-- 输入
SELECT first_name, last_name, salary FROM employees;

-- 输出
first_name | last_name | salary
-----------+-----------+--------
张         | 三        | 5000
李         | 四        | 6000
王         | 五        | 5500

3. 处理多表查询中的列名冲突

当查询涉及多个表且存在同名列时，必须使用表名前缀来区分：

sql

-- 错误示例：列名不明确
SELECT name, name FROM employees, departments;

-- 正确示例：使用表名前缀
SELECT employees.name, departments.name
FROM employees, departments;

-- 或者使用表别名
SELECT e.name, d.name
FROM employees e, departments d;

完整示例：

sql

-- 假设有两个表：employees 和 departments
-- employees: employee_id, name, department_id
-- departments: department_id, name, manager_id

SELECT
    e.name AS employee_name,
    d.name AS department_name,
    e.department_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

4. 选择特定表的所有列

在多表查询中，可以选择特定表的所有列：

sql

-- 选择 employees 表的所有列和 departments 表的 name 列
SELECT employees.*, departments.name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

表达式作为选择列表项

选择列表中可以使用任意的值表达式，不仅限于简单的列引用：

1. 算术表达式

sql

-- 计算年薪和税后收入
SELECT
    first_name,
    last_name,
    salary,
    salary * 12 AS annual_salary,
    salary * 0.8 AS after_tax_salary
FROM employees;

2. 字符串操作

sql

-- 拼接姓名
SELECT
    first_name || ' ' || last_name AS full_name,
    upper(first_name) AS upper_first_name,
    length(first_name) AS name_length
FROM employees;

3. 函数调用

sql

-- 使用各种函数
SELECT
    first_name,
    salary,
    round(salary * 1.1, 2) AS increased_salary,
    current_date AS query_date,
    age(hire_date) AS employment_duration
FROM employees;

4. 条件表达式

sql

-- 使用 CASE 表达式
SELECT
    first_name,
    last_name,
    salary,
    CASE
        WHEN salary > 6000 THEN '高薪'
        WHEN salary > 4000 THEN '中薪'
        ELSE '低薪'
    END AS salary_level
FROM employees;

5. 常量表达式

sql

-- 包含常量
SELECT
    'Employee' AS record_type,
    first_name,
    last_name,
    42 AS magic_number,
    pi() AS pi_value
FROM employees;

完整示例分析：

sql

-- 复杂的选择列表示例
SELECT
    -- 基本列
    employee_id,
    first_name || ' ' || last_name AS full_name,

    -- 算术计算
    salary AS monthly_salary,
    salary * 12 AS annual_salary,
    round(salary * 12 * 0.85, 2) AS net_annual_salary,

    -- 条件逻辑
    CASE
        WHEN salary > 6000 THEN 'Senior'
        WHEN salary > 4000 THEN 'Middle'
        ELSE 'Junior'
    END AS level,

    -- 日期函数
    hire_date,
    extract(year from age(hire_date)) AS years_employed,

    -- 常量
    'Active' AS status,
    current_timestamp AS report_generated_at

FROM employees
WHERE department = 'IT';

预期输出：

employee_id	full_name	monthly_salary	annual_salary	net_annual_salary	level	hire_date	years_employed	status	report_generated_at
1	张三	5000	60000	51000.00	Middle	2023-01-15	1	Active	2024-05-29 10:30:00
3	王五	5500	66000	56100.00	Middle	2023-05-10	1	Active	2024-05-29 10:30:00

列标签（AS 子句）

基本语法

列标签用于为选择列表中的项目指定输出名称：

sql

SELECT column_name AS label_name FROM table_name;

何时需要使用列标签

1. 为复杂表达式提供有意义的名称

sql

-- 没有标签的复杂表达式
SELECT salary * 12 * 0.85 FROM employees;
-- 列名将是：?column?

-- 使用标签提供清晰的名称
SELECT salary * 12 * 0.85 AS net_annual_income FROM employees;
-- 列名将是：net_annual_income

2. 重命名现有列

sql

-- 为现有列提供更友好的名称
SELECT
    first_name AS "名",
    last_name AS "姓",
    salary AS "月薪"
FROM employees;

3. 避免与关键字冲突

sql

-- 错误：FROM 是关键字
SELECT a from, b + c AS sum FROM table1;

-- 正确：使用 AS 或双引号
SELECT a AS from, b + c AS sum FROM table1;
SELECT a "from", b + c AS sum FROM table1;

AS 关键字的使用规则

1. AS 关键字通常是可选的

sql

-- 以下两种写法等效
SELECT first_name AS name FROM employees;
SELECT first_name name FROM employees;

2. 何时必须使用 AS

注意在以下情况下必须使用 AS 关键字或双引号：

标签名称是 PostgreSQL 关键字
标签名称包含空格或特殊字符
标签名称以数字开头

sql

-- 必须使用 AS 的情况
SELECT
    salary AS "from",          -- from 是关键字
    bonus AS "annual bonus",   -- 包含空格
    department AS "2023_dept"  -- 以数字开头（虽然这里不是）
FROM employees;

标签名称的最佳实践

1. 使用有意义的名称

sql

-- 好的标签名称
SELECT
    first_name AS employee_first_name,
    last_name AS employee_last_name,
    salary * 12 AS annual_salary,
    hire_date AS employment_start_date
FROM employees;

-- 避免无意义的名称
SELECT
    first_name AS col1,
    last_name AS col2,
    salary * 12 AS calc1
FROM employees;

2. 保持命名一致性

sql

-- 一致的命名风格
SELECT
    employee_id AS emp_id,
    first_name AS emp_first_name,
    last_name AS emp_last_name,
    department_id AS dept_id
FROM employees;

双重重命名

信息 PostgreSQL 允许列被重命名两次：

在 FROM 子句中（表别名.列别名）
在 SELECT 子句中（AS 标签）

最终传递的名称是 SELECT 子句中分配的名称。

sql

-- 双重重命名示例
SELECT
    e.emp_name AS employee_full_name  -- 最终列名是 employee_full_name
FROM (
    SELECT first_name || ' ' || last_name AS emp_name  -- 中间列名是 emp_name
    FROM employees
) e;

DISTINCT 去重

基本 DISTINCT

DISTINCT 关键字用于消除结果集中的重复行：

sql

-- 基本语法
SELECT DISTINCT column1, column2, ... FROM table_name;

示例演示

假设 employees 表中有以下数据：

employee_id	department	salary
1	IT	5000
2	HR	6000
3	IT	5500
4	IT	5000
5	Finance	5500

sql

-- 查找所有不重复的部门
SELECT DISTINCT department FROM employees;

-- 输出
department
----------
IT
HR
Finance

-- 查找所有不重复的薪资
SELECT DISTINCT salary FROM employees;

-- 输出
salary
------
5000
6000
5500

多列 DISTINCT

当指定多个列时，只有当所有指定列的值都相同时，行才被认为是重复的：

sql

-- 查找不重复的部门-薪资组合
SELECT DISTINCT department, salary FROM employees;

-- 输出
department | salary
-----------+--------
IT         | 5000
HR         | 6000
IT         | 5500
Finance    | 5500

分析过程：

IT + 5000 出现两次（employee_id 1 和 4），但只保留一行
其他组合都是唯一的，所以都被保留

DISTINCT ON 子句

DISTINCT ON 是 PostgreSQL 特有的扩展，允许基于特定表达式进行去重：

sql

-- 语法
SELECT DISTINCT ON (expression [, expression ...]) select_list FROM table_name;

基本用法

sql

-- 为每个部门保留一个员工记录
SELECT DISTINCT ON (department)
    department,
    first_name,
    salary
FROM employees
ORDER BY department, salary DESC;

重要说明：

DISTINCT ON 处理发生在 ORDER BY 排序之后
对于每组相同的 expression 值，只保留第一行
必须配合 ORDER BY 使用以确保结果的确定性

完整示例

sql

-- 查找每个部门薪资最高的员工
SELECT DISTINCT ON (department)
    department,
    first_name,
    last_name,
    salary
FROM employees
ORDER BY department, salary DESC;

数据示例：

输入数据： | department | first_name | last_name | salary | |------------|------------|-----------|--------| | Finance | 赵 | 六 | 7000 | | HR | 李 | 四 | 6000 | | IT | 张 | 三 | 5000 | | IT | 王 | 五 | 5500 | | IT | 陈 | 七 | 6500 |

sql

SELECT DISTINCT ON (department)
    department,
    first_name,
    last_name,
    salary
FROM employees
ORDER BY department, salary DESC;

输出结果： | department | first_name | last_name | salary | |------------|------------|-----------|--------| | Finance | 赵 | 六 | 7000 | | HR | 李 | 四 | 6000 | | IT | 陈 | 七 | 6500 |

DISTINCT 与 NULL 值

重要概念在 DISTINCT 比较中，NULL 值被认为是相等的：

sql

-- 假设有包含 NULL 的数据
INSERT INTO test_table (value) VALUES (1), (2), (NULL), (NULL), (1);

SELECT DISTINCT value FROM test_table;

-- 输出
value
-------
1
2
(null)

DISTINCT 的性能考虑

1. 索引的影响

sql

-- 在有索引的列上使用 DISTINCT 性能更好
CREATE INDEX idx_department ON employees(department);

SELECT DISTINCT department FROM employees;  -- 性能较好

2. 与 GROUP BY 的比较

sql

-- DISTINCT 方式
SELECT DISTINCT department FROM employees;

-- GROUP BY 方式（功能相同，有时性能更好）
SELECT department FROM employees GROUP BY department;

性能提示

对于简单的去重，DISTINCT 和 GROUP BY 性能相似
GROUP BY 在需要聚合函数时更合适
DISTINCT ON 是 PostgreSQL 特有功能，在其他数据库中需要用子查询替代

实际应用场景

1. 数据探索

sql

-- 探索数据的唯一值
SELECT DISTINCT
    department,
    COUNT(*) OVER (PARTITION BY department) AS dept_count
FROM employees;

2. 报表生成

sql

-- 生成部门薪资报告（每个部门最高薪资员工）
SELECT DISTINCT ON (department)
    department AS "部门",
    first_name || ' ' || last_name AS "最高薪资员工",
    salary AS "薪资"
FROM employees
ORDER BY department, salary DESC;

3. 数据清洗

sql

-- 在数据迁移前检查重复数据
SELECT
    email,
    COUNT(*) as duplicate_count
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

-- 清理重复数据，保留最新的记录
DELETE FROM employees
WHERE employee_id NOT IN (
    SELECT DISTINCT ON (email) employee_id
    FROM employees
    ORDER BY email, created_at DESC
);

高级应用和最佳实践

选择列表中的窗口函数

sql

-- 在选择列表中使用窗口函数
SELECT
    employee_id,
    first_name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

子查询在选择列表中的应用

sql

-- 标量子查询
SELECT
    employee_id,
    first_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS salary_diff
FROM employees;

动态列选择

虽然 PostgreSQL 不直接支持动态 SQL 在普通查询中，但可以通过存储过程实现：

sql

-- 使用数组来动态构建选择列表
DO $$
DECLARE
    columns_to_select text[] := ARRAY['first_name', 'last_name', 'salary'];
    sql_query text;
BEGIN
    sql_query := 'SELECT ' || array_to_string(columns_to_select, ', ') ||
                 ' FROM employees';
    RAISE NOTICE 'Generated SQL: %', sql_query;
    -- EXECUTE sql_query; -- 在实际应用中执行
END $$;

性能优化建议

1. 只选择需要的列

sql

-- 好的做法：只选择需要的列
SELECT employee_id, first_name, salary FROM employees;

-- 避免：选择所有列（除非确实需要）
SELECT * FROM employees;

2. 合理使用表达式

sql

-- 将复杂计算移到 WHERE 子句或使用预计算列
-- 而不是在 SELECT 中进行复杂计算

-- 较差的性能
SELECT *, very_complex_function(col1, col2, col3) AS result
FROM large_table;

-- 更好的做法
SELECT *
FROM (
    SELECT *, very_complex_function(col1, col2, col3) AS result
    FROM large_table
    WHERE simple_condition = true  -- 先过滤数据
) subquery
WHERE result > threshold;

3. 索引友好的 DISTINCT

sql

-- 确保 DISTINCT 的列有适当的索引
CREATE INDEX idx_dept_salary ON employees(department, salary);

SELECT DISTINCT ON (department) *
FROM employees
ORDER BY department, salary DESC;

总结

选择列表是 PostgreSQL 查询的核心组件，它提供了灵活而强大的功能：

基本功能：选择特定列、所有列或使用表达式创建计算列
列标签：为输出列提供有意义的名称，增强查询结果的可读性
去重机制：通过 DISTINCT 和 DISTINCT ON 实现数据去重
表达式支持：支持算术运算、函数调用、条件逻辑等复杂表达式

重要提醒

在生产环境中避免使用 SELECT *
合理使用列标签提高查询结果的可读性
DISTINCT ON 是 PostgreSQL 特有功能，注意跨数据库兼容性
配合适当的索引优化 DISTINCT 查询性能

通过掌握这些选择列表的知识和技巧，您可以编写出更高效、更可读的 PostgreSQL 查询。

选择列表 ​

概述 ​

选择列表项 ​

基本用法 ​

1. 选择所有列（*） ​

2. 选择指定列 ​

3. 处理多表查询中的列名冲突 ​

4. 选择特定表的所有列 ​

表达式作为选择列表项 ​

1. 算术表达式 ​

2. 字符串操作 ​

3. 函数调用 ​

4. 条件表达式 ​

5. 常量表达式 ​

列标签（AS 子句） ​

基本语法 ​

何时需要使用列标签 ​

1. 为复杂表达式提供有意义的名称 ​

2. 重命名现有列 ​

3. 避免与关键字冲突 ​

AS 关键字的使用规则 ​

1. AS 关键字通常是可选的 ​

2. 何时必须使用 AS ​

标签名称的最佳实践 ​

1. 使用有意义的名称 ​

2. 保持命名一致性 ​

双重重命名 ​

DISTINCT 去重 ​

基本 DISTINCT ​

示例演示 ​

多列 DISTINCT ​

DISTINCT ON 子句 ​

基本用法 ​

完整示例 ​

DISTINCT 与 NULL 值 ​

DISTINCT 的性能考虑 ​

1. 索引的影响 ​

2. 与 GROUP BY 的比较 ​

实际应用场景 ​

1. 数据探索 ​

2. 报表生成 ​

3. 数据清洗 ​

高级应用和最佳实践 ​

选择列表中的窗口函数 ​

子查询在选择列表中的应用 ​

动态列选择 ​

性能优化建议 ​

1. 只选择需要的列 ​

2. 合理使用表达式 ​

3. 索引友好的 DISTINCT ​

总结 ​

选择列表

概述

选择列表项

基本用法

1. 选择所有列（*）

2. 选择指定列

3. 处理多表查询中的列名冲突

4. 选择特定表的所有列

表达式作为选择列表项

1. 算术表达式

2. 字符串操作

3. 函数调用

4. 条件表达式

5. 常量表达式

列标签（AS 子句）

基本语法

何时需要使用列标签

1. 为复杂表达式提供有意义的名称

2. 重命名现有列

3. 避免与关键字冲突

AS 关键字的使用规则

1. AS 关键字通常是可选的

2. 何时必须使用 AS

标签名称的最佳实践

1. 使用有意义的名称

2. 保持命名一致性

双重重命名

DISTINCT 去重

基本 DISTINCT

示例演示

多列 DISTINCT

DISTINCT ON 子句

基本用法

完整示例

DISTINCT 与 NULL 值

DISTINCT 的性能考虑

1. 索引的影响

2. 与 GROUP BY 的比较

实际应用场景

1. 数据探索

2. 报表生成

3. 数据清洗

高级应用和最佳实践

选择列表中的窗口函数

子查询在选择列表中的应用

动态列选择

性能优化建议

1. 只选择需要的列

2. 合理使用表达式

3. 索引友好的 DISTINCT

总结