PostgreSQL 别名（Aliases）

什么是别名？

别名是为表或列指定的临时名称，用于使查询更易读或更简洁。别名仅在查询执行期间存在。

列别名

基本语法

SELECT column_name AS alias_name
FROM table_name;

-- 或省略AS关键字
SELECT column_name alias_name
FROM table_name;

列别名示例

-- 使用AS关键字
SELECT 
    first_name AS 名字,
    last_name AS 姓氏,
    email AS 电子邮件
FROM users;

-- 省略AS关键字
SELECT 
    first_name 名字,
    last_name 姓氏,
    email 电子邮件
FROM users;

-- 带空格的别名（使用引号）
SELECT 
    first_name AS "客户名字",
    last_name AS "客户姓氏",
    email AS "电子邮件地址"
FROM users;

计算列的别名

-- 算术运算
SELECT 
    product_name AS 产品名称,
    price AS 原价,
    price * 0.9 AS 折扣价,
    price * 0.1 AS 节省金额
FROM products;

-- 字符串连接
SELECT 
    first_name || ' ' || last_name AS 全名,
    UPPER(email) AS 大写邮箱
FROM users;

-- 聚合函数
SELECT 
    COUNT(*) AS 总用户数,
    AVG(age) AS 平均年龄,
    MAX(salary) AS 最高工资,
    MIN(salary) AS 最低工资
FROM employees;

使用函数的别名

-- 日期函数
SELECT 
    order_date AS 订单日期,
    EXTRACT(YEAR FROM order_date) AS 年份,
    EXTRACT(MONTH FROM order_date) AS 月份,
    AGE(order_date) AS 距今时间
FROM orders;

-- 字符串函数
SELECT 
    name AS 原始名称,
    UPPER(name) AS 大写名称,
    LOWER(name) AS 小写名称,
    LENGTH(name) AS 名称长度
FROM products;

表别名

基本语法

SELECT alias.column_name
FROM table_name AS alias;

-- 或省略AS关键字
SELECT alias.column_name
FROM table_name alias;

表别名示例

-- 简单表别名
SELECT 
    u.id,
    u.name,
    u.email
FROM users AS u;

-- 省略AS关键字
SELECT 
    u.id,
    u.name,
    u.email
FROM users u;

-- 多表查询中的别名
SELECT 
    u.name AS 用户名,
    o.order_number AS 订单号,
    o.total AS 订单金额
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

复杂查询中的表别名

-- 三表连接
SELECT 
    c.name AS 客户名称,
    o.order_number AS 订单号,
    p.product_name AS 产品名称,
    oi.quantity AS 数量,
    oi.price AS 单价
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- 自连接（必须使用别名）
SELECT 
    e1.name AS 员工,
    e2.name AS 经理
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

子查询中的别名

派生表别名

-- 子查询必须有别名
SELECT 
    dept_stats.department,
    dept_stats.avg_salary,
    dept_stats.employee_count
FROM (
    SELECT 
        department,
        AVG(salary) AS avg_salary,
        COUNT(*) AS employee_count
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE dept_stats.avg_salary > 50000;

CTE（公用表表达式）别名

-- WITH子句中的别名
WITH high_value_customers AS (
    SELECT 
        customer_id,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total) > 10000
)
SELECT 
    c.name AS 客户名称,
    hvc.total_spent AS 总消费
FROM high_value_customers hvc
INNER JOIN customers c ON hvc.customer_id = c.id;

在不同子句中使用别名

WHERE 子句

-- 注意：不能在WHERE中直接使用列别名
-- 错误示例
SELECT 
    price * quantity AS total
FROM order_items
WHERE total > 100;  -- 错误！

-- 正确方法：重复表达式
SELECT 
    price * quantity AS total
FROM order_items
WHERE price * quantity > 100;

-- 或使用子查询
SELECT * FROM (
    SELECT 
        price * quantity AS total
    FROM order_items
) AS items
WHERE total > 100;

ORDER BY 子句

-- 可以在ORDER BY中使用列别名
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

-- 也可以使用列位置
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary * 12 AS annual_salary
FROM employees
ORDER BY 2 DESC;  -- 按第2列排序

GROUP BY 子句

-- 不能在GROUP BY中直接使用列别名
-- 错误示例
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    COUNT(*) AS order_count
FROM orders
GROUP BY year;  -- 错误！

-- 正确方法：重复表达式
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);

-- PostgreSQL特有：可以使用列位置
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    COUNT(*) AS order_count
FROM orders
GROUP BY 1;

HAVING 子句

-- 可以在HAVING中使用聚合别名（PostgreSQL扩展）
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 50000;  -- PostgreSQL允许

-- 标准SQL方法（更兼容）
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

实际应用场景

场景 1：报表生成

SELECT 
    p.product_name AS "产品名称",
    c.category_name AS "类别",
    COUNT(oi.id) AS "销售次数",
    SUM(oi.quantity) AS "总销量",
    SUM(oi.quantity * oi.price) AS "总销售额",
    AVG(oi.price) AS "平均单价"
FROM products p
INNER JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.product_name, c.category_name
ORDER BY "总销售额" DESC;

场景 2：数据分析

SELECT 
    DATE_TRUNC('month', order_date) AS 月份,
    COUNT(DISTINCT customer_id) AS 活跃客户数,
    COUNT(*) AS 订单总数,
    SUM(total) AS 月度收入,
    AVG(total) AS 平均订单金额
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY 月份
ORDER BY 月份 DESC;

场景 3：复杂计算

SELECT 
    e.name AS 员工姓名,
    e.salary AS 基本工资,
    e.salary * 0.1 AS 奖金,
    e.salary * 1.1 AS 总收入,
    d.name AS 部门名称,
    (
        SELECT AVG(salary) 
        FROM employees 
        WHERE department_id = e.department_id
    ) AS 部门平均工资,
    e.salary - (
        SELECT AVG(salary) 
        FROM employees 
        WHERE department_id = e.department_id
    ) AS 与平均工资差额
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

别名命名规范

好的实践

-- 使用有意义的名称
SELECT 
    customer_id AS cust_id,
    first_name AS fname,
    last_name AS lname
FROM customers;

-- 表别名使用简短但清晰的名称
SELECT 
    c.name,
    o.order_number,
    p.product_name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

避免的做法

-- 避免：过于简短，不清晰
SELECT 
    a.x,
    b.y
FROM table1 a
INNER JOIN table2 b ON a.id = b.id;

-- 避免：使用保留字
SELECT 
    name AS select,  -- 不好
    price AS from    -- 不好
FROM products;

-- 避免：过长的别名
SELECT 
    name AS this_is_a_very_long_and_unnecessary_alias_name
FROM products;

特殊情况

带引号的别名

-- 需要引号的情况：
-- 1. 包含空格
SELECT name AS "Product Name" FROM products;

-- 2. 包含特殊字符
SELECT price AS "Price ($)" FROM products;

-- 3. 保留关键字
SELECT status AS "Order" FROM orders;

-- 4. 区分大小写（PostgreSQL默认不区分）
SELECT name AS "ProductName" FROM products;

数字别名

-- 可以使用数字开头（需要引号）
SELECT 
    name AS "1st_name",
    email AS "2nd_email"
FROM users;

性能考虑

别名本身不影响查询性能，但可以：

1. 提高可读性：使复杂查询更易理解
2. 简化维护：更改表名时只需修改别名定义
3. 避免歧义：在多表连接中明确列来源

-- 使用EXPLAIN查看执行计划
EXPLAIN ANALYZE
SELECT 
    u.name AS user_name,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

最佳实践

1. 列别名：

   • 使用AS关键字提高可读性
   • 为计算列提供有意义的名称
   • 报表中使用描述性别名

2. 表别名：

   • 多表查询中始终使用表别名
   • 使用简短但清晰的别名
   • 自连接必须使用别名

3. 命名约定：

   • 使用小写和下划线
   • 避免保留关键字
   • 保持一致的命名风格

4. 可读性：

   • 复杂查询中使用别名提高可读性
   • 在JOIN条件中明确使用表别名
   • 为子查询提供有意义的别名

小结

别名是 SQL 查询的重要工具：

• 列别名：为列或表达式提供临时名称
• 表别名：简化表引用，特别是在JOIN中
• AS关键字：可选但推荐使用
• 引号：包含空格或特殊字符时需要
• 作用域：仅在查询执行期间有效
• 限制：某些子句中不能直接使用别名

掌握别名的使用可以使 SQL 查询更清晰、更易维护。