問題描述

所以這實際上不是我的代碼，而只是我正在嘗試做的一個例子.理想情況下，我可以使用 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 = ...

這篇關于SQL - 在子查詢的 where 子句中使用別名的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！