如何在SQL Server表中不存在的列上使用“WHERE”条件执行“SELECT”查询
问题描述:
要理解此问题,我将举例说明:Students
表中的列为:如何在SQL Server表中不存在的列上使用“WHERE”条件执行“SELECT”查询
stuID, cityID, Name, updateDate
,我的选择是:
SELECT
ROW_NUMBER() OVER (PARTITION BY cityID ORDER BY updateDate DESC) AS rownumber
stuID,
cityID,
Name
FROM
Students
WHERE
rownumber = 1
不管 - 我为什么要做出这样的查询,这是唯一的例子,但我怎么能摆在ROWNUMBER的“WHERE”条件??? ?
答
的WHERE
条款将后FROM
条款立即计算,所以SQL不知道,当你是指someValue中你选择
使用CTE /子查询refered如果你想申请谓词ROWNUMBER:
;With cte
as
(
SELECT
ROW_NUMBER() OVER (PARTITION BY cityID ORDER BY updateDate DESC) AS rownumber
stuID,
cityID,
Name
FROM Students
)
select * from cte where rownumber=1
下面是合乎逻辑的查询阶段,这就决定了如何在一个阶段定义的条款在下一阶段提供给条款..
1. FROM
2. ON
3.OUTER
4.WHERE
5.GROUP BY
6.CUBE | ROLLUP
7.HAVING
8. SELECT
9. DISTINCT
10. TOP
11. ORDER BY
正如你所看到的,RowNumber
您在select
条款将只提供给下一个阶段后选择
Below is the Logical query processing flow chart for each clause :Itzik Ben-Gan
答
任何别名和计算领域,如果你把你的查询都可以定义纳入子查询或CTE:
select *
from
(
SELECT
ROW_NUMBER() OVER (PARTITION BY cityID ORDER BY updateDate DESC) AS rownumber
stuID,
cityID,
Name
FROM Students
) s
WHERE rownumber = 1