为什么不能WHERE子句中使用ROW_NUMBER（）

这篇文章给大家介绍为什么不能WHERE子句中使用ROW_NUMBER（），内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

主要原因： SQL的执行顺序

Here’s a common coding scenario for SQL Server developers:

“I want to see the oldest amount due for each account, along with the account number and due date, ordered by account number.”

Since the release of SQL Server 2005, the simplest way to do this has been to use a window function like ROW_NUMBER.  In many cases, everything you need can be done in a single SELECT statement with your window function. The trouble comes when you want to incorporate that function in some other way. For instance, using it a WHERE clause.

Let’s use this example from AdventureWorks2012:

This works perfectly and gives us every row in the table. Now, let’s modify it to meet the scenario we outlined earlier, and only show the oldest order for each account.

Because the WHERE clause already happened.

Logical Query Processing

SQL Server doesn’t process parts of a query in the same order they’re written. Rather than start with SELECT the way we read and write it, here’s the order SQL Server progresses through:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

7. TOP

The first four steps are all about getting the source data and reducing the result set down. Steps 5 & 6 determine which columns are presented and in which order. Step 7 (TOP) is only applied at the end because you can’t say which rows are in the top n rows until the set has been sorted. (You can read Itzik Ben-Gan’s explanation of this process in way more detail here.)

Since the WHERE clause happens before the SELECT, it’s too late in the process to add the window function to the WHERE clause. It’d have to loop back around a second time to re-evaluate WHERE.

There’s No Magic Hack

Unfortunately, the logical query processing model is a fundamental way of doing business for SQL Server, so we can’t just cheat around it. Instead, we have to reference our window function through a subquery or CTE (common table expression). In other words, we have to code in a way that makes SQL Server evaluate a WHERE clause after the SELECT containing the window function:

By putting the WHERE clause in the outer query and the window function in the subquery (also called an inner query), we get the window function to come before the WHERE.

关于为什么不能WHERE子句中使用ROW_NUMBER（）就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。