在MySQL关联查询中,别名主要有以下几个重要作用:
简化表名和列名
SELECT u.username FROM user AS u WHERE u.id = 1
处理自连接查询
SELECT a.name, b.name FROM employee a, employee b WHERE a.manager_id = b.id
列名重命名
SELECT COUNT(*) AS user_count FROM users
提高可读性
分组在关联查询中主要用于:
聚合计算
SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY department_id
数据分类汇总
SELECT product_category, SUM(sales) FROM orders GROUP BY product_category
去重显示
HAVING筛选
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 5000
-- 使用别名和分组的复杂关联查询示例
SELECT
d.department_name AS dept_name,
COUNT(e.employee_id) AS emp_count,
AVG(e.salary) AS avg_salary,
MAX(e.hire_date) AS latest_hire
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
GROUP BY
d.department_name
HAVING
COUNT(e.employee_id) > 5
ORDER BY
avg_salary DESC;
在这个示例中:
- 使用了表别名e
和d
简化查询
- 使用列别名提高结果可读性
- 按部门名称分组计算各项统计值
- 使用HAVING筛选员工数大于5的部门