為什么不能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（）就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。