PostgreSQL 别名的使用_MsSqlserver

别名是 sql 的“命名魔法”，让查询更简洁、可读、可复用。
本文涵盖列别名、表别名、cte 别名、函数别名、表达式别名、json 别名、窗口函数别名、命名规范、性能影响、常见陷阱、最佳实践等全部内容。

一、别名三大类型

类型	语法	作用
列别名	column as alias	重命名输出列
表别名	table as alias	简化表名，解决冲突
cte 别名	with cte_name as (...)	命名子查询

二、准备测试数据

create table employees (
    id bigserial primary key,
    full_name text,
    email_address text,
    dept_code varchar(10),
    annual_salary numeric(10,2),
    hire_date date,
    tags text[]
);

insert into employees (full_name, email_address, dept_code, annual_salary, hire_date, tags) values
('alice johnson', 'alice.j@company.com', 'it', 85000, '2023-01-15', '{"dev","lead"}'),
('bob smith',     'bob.s@company.com',   'hr', 52000, '2022-06-10', '{"hr"}'),
('carol white',   'carol.w@company.com', 'it', 92000, '2024-03-20', '{"dev","ai"}'),
('dave brown',    null,                  'it', 78000, '2021-11-05', '{"dev"}'),
('eve davis',     'eve.d@company.com',   'hr', 48000, '2023-12-01', '{"hr","recruit"}');

三、列别名（column alias）

1. 基本语法

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';

到此这篇关于postgresql 别名的使用的文章就介绍到这了,更多相关postgresql 别名内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

PostgreSQL 别名的使用

2025年11月06日 • MsSqlserver •我要评论