SELECT
full_name AS name,
annual_salary AS salary,
hire_date AS "Hire Date" -- 保留空格/大小写
FROM employees;
2. 表达式别名
SELECT
full_name,
annual_salary / 12 AS monthly_salary,
UPPER(full_name) AS "FULL NAME",
COALESCE(email_address, 'N/A') AS email
FROM employees;
3. 聚合函数别名
SELECT
dept_code,
COUNT(*) AS headcount,
AVG(annual_salary) AS avg_salary,
STRING_AGG(full_name, ', ') AS team_members
FROM employees
GROUP BY dept_code;
四、表别名（Table Alias）
1. 简化长表名
SELECT e.full_name, e.annual_salary
FROM employees AS e;
-- 等价于
FROM employees e;
2. 自连接（Self-Join）
-- 员工与经理
SELECT
e.full_name AS employee,
m.full_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
3. 多表 JOIN 避免冲突
SELECT
e.full_name,
d.name AS dept_name
FROM employees e
JOIN departments d ON e.dept_code = d.code;
五、CTE 别名（WITH 子句）
WITH
it_employees AS (
SELECT * FROM employees WHERE dept_code = 'IT'
),
high_earners AS (
SELECT full_name, annual_salary
FROM it_employees
WHERE annual_salary > 80000
)
SELECT * FROM high_earners;
六、函数与窗口函数别名
SELECT
full_name,
EXTRACT(YEAR FROM hire_date) AS hire_year,
ROW_NUMBER() OVER (PARTITION BY dept_code ORDER BY annual_salary DESC) AS rank_in_dept
FROM employees;
七、JSON 与数组别名
SELECT
full_name,
tags[1] AS primary_tag,
config->>'level' AS "Employee Level"
FROM employees;
八、别名命名规范（推荐）
类型
规范
示例
表别名
1-3 个字母，表名缩写
e, emp, d, dept
列别名
清晰、驼峰或下划线
employee_name, deptName
CTE 别名
动词 + 名词
active_employees, monthly_sales
避免
保留字、空格开头
order, group
-- 推荐
SELECT e.full_name AS employee_name FROM employees e;
-- 不推荐
SELECT employees.full_name AS name FROM employees; -- 太长
九、别名与 ORDER BY
1. 支持别名（推荐）
SELECT full_name, annual_salary AS salary
FROM employees
ORDER BY salary DESC;
2. 不支持列序号（不推荐）
ORDER BY 2 -- 模糊，易出错
十、性能影响：几乎为 0
项目
影响
列别名
无（仅输出层）
表别名
无（优化器内部使用）
CTE 别名
可能物化（MATERIALIZED）
-- 性能相同
SELECT full_name AS name FROM employees;
SELECT full_name FROM employees;
十一、常见陷阱与解决方案
陷阱
说明
解决方案
AS 省略导致歧义
SELECT col AS FROM table
始终加 AS
别名与保留字冲突
SELECT col AS order
用双引号 "order"
GROUP BY 用别名
不允许
用原始列或列序号
WHERE 用别名
不允许
用原始表达式
-- 错误：WHERE 不能用别名
SELECT full_name, annual_salary AS salary
FROM employees
WHERE salary > 80000; -- 语法错误！
-- 正确
SELECT full_name, annual_salary AS salary
FROM employees
WHERE annual_salary > 80000;
-- GROUP BY 别名错误
SELECT dept_code, COUNT(*) AS cnt
FROM employees
GROUP BY cnt; -- 错误！
-- 正确
GROUP BY dept_code;
-- 或
GROUP BY 1; -- 列序号
十二、最佳实践 Checklist
项目
建议
表别名必加
提升可读性
列别名清晰
避免 col1, col2
AS 显式使用
避免歧义
ORDER BY 用别名
简洁
WHERE/GROUP BY 用原始列
符合语法
CTE 别名动词化
filtered_data, aggregated_sales
避免保留字
用 "order", "group"
十三、一键生产级查询模板
WITH active_employees AS (
SELECT
id,
full_name AS employee_name,
email_address AS email,
dept_code,
annual_salary AS salary,
hire_date
FROM employees
WHERE hire_date >= '2023-01-01'
),
dept_stats AS (
SELECT
dept_code,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary
FROM active_employees
GROUP BY dept_code
)
SELECT
ae.employee_name,
ae.email,
ae.salary,
ds.headcount,
ds.avg_salary,
RANK() OVER (PARTITION BY ae.dept_code ORDER BY ae.salary DESC) AS salary_rank
FROM active_employees ae
JOIN dept_stats ds ON ae.dept_code = ds.dept_code
ORDER BY ds.avg_salary DESC, ae.salary DESC;
十四、快速实战：5 分钟掌握别名
-- 1. 列别名
SELECT full_name AS name, annual_salary AS salary FROM employees;
-- 2. 表别名 + 自连接
SELECT e.full_name AS emp, m.full_name AS mgr
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- 3. CTE 别名
WITH it_team AS (SELECT * FROM employees WHERE dept_code = 'IT')
SELECT employee_name, salary FROM it_team;
-- 4. 窗口函数别名
SELECT
full_name,
dept_code,
ROW_NUMBER() OVER (PARTITION BY dept_code ORDER BY annual_salary DESC) AS rank
FROM employees;
-- 5. ORDER BY 别名
SELECT annual_salary * 12 AS yearly
FROM employees
ORDER BY yearly DESC;
现在就动手： 在 employees 表中：
查询 full_name → name, annual_salary → salary
自连接查询员工与经理名（用 e 和 m 别名）
用 CTE 别名 high_earners 筛选 salary > 80000
计算月薪并用别名 monthly，按其降序
用 RANK() 给每个部门薪资排名，用别名 dept_rank
十五、别名 vs 视图 vs 函数
方式
别名
视图
函数
复用性
仅当前查询
高
高
参数化
No
No
Yes
性能
最高
高
中等
适用
临时命名
固定查询
复杂逻辑
-- 视图替代 CTE 别名
CREATE VIEW v_active_employees AS
SELECT id, full_name AS name, annual_salary AS salary
FROM employees WHERE hire_date >= '2023-01-01';