如何在相关子查询中使用别名? [英] How to use an alias in Correlated subquery?

问题描述

mysql>从导师中选择*；+-------+------------+------------+---------+|身份证 |姓名 |部门名称 |工资|+-------+------------+------------+---------+|10101 |斯里尼瓦桑 |比较科学.|65000.00 ||12121 |吴|财务 |90000.00 ||15151 |莫扎特 |音乐 |40000.00 ||22222 |爱因斯坦 |物理 |95000.00 ||32343 |埃尔赛义德 |历史 |60000.00 ||33456 |黄金 |物理 |87000.00 ||45565 |卡茨 |比较科学.|75000.00 ||58583 |卡利菲利 |历史 |62000.00 ||76543 |辛格 |财务 |80000.00 ||76766 |克里克 |生物学 |72000.00 ||83821 |布兰特 |比较科学.|92000.00 ||98345 |金|电子英.|80000.00 |+-------+------------+------------+---------+

我有一个这样的 SQL 语句

从讲师 t1 中选择姓名、薪水、部门名称工资 >(选择平均(工资)来自老师 t2按部门名称分组有 t1.dept_name = t2.dept_name)+------------+------------+------------+|姓名 |工资|部门名称 |+------------+------------+------------+|吴|90000.00 |财务 ||爱因斯坦 |95000.00 |物理 ||卡利菲利 |62000.00 |历史 ||布兰特 |92000.00 |比较科学.|+------------+------------+------------+

我想在外部查询中添加一个 alias(dept_avg_salary) 但失败了.....

从教师 t1 中选择姓名、工资、部门名称、部门平均工资工资 >(选择 avg(salary) 作为 dept_avg_salary来自老师 t2按部门名称分组有 t1.dept_name = t2.dept_name)

<块引用>

错误 1054 (42S22):字段列表"中的未知列dept_avg_salary"

谁能告诉我为什么，非常感谢！

解决方案

您不能引用这样的别名.这是使用带有 join 的子查询的更好方法:

选择t1.name, t1.salary, t1.dept_name, t2.dept_avg_salary从教师 t1 加入 (选择部门名称，平均工资(薪水)dept_avg_salary来自导师按部门名称分组)t2 上 t1.dept_name = t2.dept_name其中 t1.salary >t2.dept_avg_salary

• SQL Fiddle 演示

mysql> select * from instructor;

+-------+------------+------------+----------+
| ID    | name       | dept_name  | salary   |
+-------+------------+------------+----------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 |
| 12121 | Wu         | Finance    | 90000.00 |
| 15151 | Mozart     | Music      | 40000.00 |
| 22222 | Einstein   | Physics    | 95000.00 |
| 32343 | El Said    | History    | 60000.00 |
| 33456 | Gold       | Physics    | 87000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 |
| 58583 | Califieri  | History    | 62000.00 |
| 76543 | Singh      | Finance    | 80000.00 |
| 76766 | Crick      | Biology    | 72000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 |
+-------+------------+------------+----------+

and I have a SQL statement like this

select name,salary,dept_name from instructor t1 
where salary > 
(
   select avg(salary) 
   from instructor t2
   group by dept_name   
   having t1.dept_name = t2.dept_name 
)
+-----------+----------+------------+
| name      | salary   | dept_name  |
+-----------+----------+------------+
| Wu        | 90000.00 | Finance    |
| Einstein  | 95000.00 | Physics    |
| Califieri | 62000.00 | History    |
| Brandt    | 92000.00 | Comp. Sci. |
+-----------+----------+------------+

I want to add an alias(dept_avg_salary) in outer query but fails.....

select name,salary,dept_name,dept_avg_salary from instructor t1 
where salary > 
(
   select avg(salary) as dept_avg_salary
   from instructor t2
   group by dept_name
   having t1.dept_name = t2.dept_name 
) 

ERROR 1054 (42S22): Unknown column 'dept_avg_salary' in 'field list'

can anybody tell me why and thank you very much!

解决方案

You cannot reference an alias like that. Here's a better approach using a subquery with a join:

select t1.name, t1.salary, t1.dept_name, t2.dept_avg_salary
from instructor t1 join (
    select dept_name, avg(salary) dept_avg_salary
    from instructor
    group by dept_name) t2 on t1.dept_name = t2.dept_name
where t1.salary > t2.dept_avg_salary

• SQL Fiddle Demo

这篇关于如何在相关子查询中使用别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！