SQL - 在子查询的 where 子句中使用别名

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

所以这实际上不是我的代码，而只是我正在尝试做的一个例子.理想情况下，我可以使用 INNER JOINS 和外键关系来获取数据，但在我的现实生活中我不能 - 这只是一个简单的例子.

So this isn't actually my code, but just an example of what I'm trying to do. Ideally I'd be able to use INNER JOINS and foreign key relations to get data, but I can't in my real-life situation - this is just a simple example.

SELECT [EmployeeID],
       [DepartmentID],
       (SELECT Title FROM Depts WHERE ID = [DepartmentID]) AS Department, 
       (SELECT Name FROM DeptHeads WHERE DeptName = Department) AS DepartmentLead
FROM   Employees E

我从一张表(员工)中获取数据.

I'm getting data from one table (Employees).

我在子查询的 where 子句中使用该表 (DepartmentID) 中的列之一，并从该 (Department) 创建别名

I'm using one of the columns from that table (DepartmentID) in a where clause in a subquery, and creating an alias from that (Department)

然后我尝试做与上面相同的事情，除了在 where 子句中使用该别名.

I'm then trying to do the same thing as above, except using that alias in the where clause.

我收到一条错误消息:

无效的列名部门"

有没有更好的方法可以做到这一点，或者有什么方法可以解决这个问题?

Is there a better way for me to do this, or a way around this?

推荐答案

您不能使用刚刚定义的别名.您可以:

You can't use aliases you just defined. You can:

SELECT * FROM (

    SELECT [EmployeeID],
               [DepartmentID],
               (SELECT Title FROM Depts WHERE ID = [DepartmentID]) AS Department, 
               (SELECT Name FROM DeptHeads WHERE DeptName = Department) AS DepartmentLead
    FROM   Employees E

) Base

WHERE Base.Department = ...

相关文章