blocks|key|327993|text|要解决此问题，请将select语句包装在CTE中，然后可以对CTE进行查询，并在where子句中使用窗口函数的结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|327994|WITH+MyCte+AS+
(
++++select+++employee_id,
+++++++++++++RowNum+=+row_number()+OVER+(+order+by+employee_id+)
++++from+++++V_EMPLOYEE+
++++ORDER+BY+Employee_ID
)
SELECT++employee_id
FROM++++MyCte
WHERE+++RowNum+>+0|code-block|syntax|javascript|327995|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To get around this issue, wrap your select statement in a CTE, and then you can query against the CTE and use the windowed function's results in the where clause.

<pre><code>WITH MyCte AS 
(
 select employee_id,
 RowNum = row_number() OVER ( order by employee_id )
 from V_EMPLOYEE 
 ORDER BY Employee_ID
)
SELECT employee_id
FROM MyCte
WHERE RowNum &gt; 0
</code></pre>

blocks|key|3301154|text|SELECT++employee_id
FROM++++(
++++++++SELECT++employee_id,+ROW_NUMBER()+OVER+(ORDER+BY+employee_id)+AS+rn
++++++++FROM++++V_EMPLOYEE
++++++++)+q
WHERE+++rn+>+0
ORDER+BY
++++++++Employee_ID|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3301155|请注意，此筛选器是冗余的：ROW_NUMBER()从1开始，并且始终大于0。|unstyled|offset|length|style|CODE|3301156|entityMap^0|0|D|C|Q|1|10|1|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]|$G|Q|H|R|I|J]|$G|S|H|T|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|U|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SELECT employee_id
FROM (
 SELECT employee_id, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
 FROM V_EMPLOYEE
 ) q
WHERE rn &gt; 0
ORDER BY
 Employee_ID
</code></pre>

Note that this filter is redundant: <code>ROW_NUMBER()</code> starts from <code>1</code> and is always greater than <code>0</code>.

blocks|key|3301365|text|Select+*+from+
(
++++Select+ROW_NUMBER()+OVER+(+order+by+Id)+as+'Row_Number',+*+
++++from+tbl_Contact_Us
)+as+tbl
Where+tbl.Row_Number+=+5|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3301366|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Select * from 
(
 Select ROW_NUMBER() OVER ( order by Id) as 'Row_Number', * 
 from tbl_Contact_Us
) as tbl
Where tbl.Row_Number = 5
</code></pre>

blocks|key|332729|text|我认为你想要这样的东西：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|332730|SELECT+employee_id+
FROM++(SELECT+employee_id,+row_number()+
+++++++OVER+(order+by+employee_id)+AS+'rownumber'+
+++++++FROM+V_EMPLOYEE)+TableExpressionsMustHaveAnAliasForDumbReasons
WHERE+rownumber+>+0|code-block|syntax|javascript|332731|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I think you want something like this:

<pre><code>SELECT employee_id 
FROM (SELECT employee_id, row_number() 
 OVER (order by employee_id) AS 'rownumber' 
 FROM V_EMPLOYEE) TableExpressionsMustHaveAnAliasForDumbReasons
WHERE rownumber &gt; 0
</code></pre>

blocks|key|3088402|text|为了回应对rexem的回答的评论，关于内联视图还是CTE更快，我重新转换了查询，使用了一个表I，每个人都有:+sys.objects。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3088403|WITH+object_rows+AS+(
++++SELECT+object_id,+
++++++++ROW_NUMBER()+OVER+(+ORDER+BY+object_id)+RN
++++FROM+sys.objects)
SELECT+object_id
FROM+object_rows
WHERE+RN+>+1

SELECT+object_id
FROM+(SELECT+object_id,+
++++++++ROW_NUMBER()+OVER+(+ORDER+BY+object_id)+RN
++++FROM+sys.objects)+T
WHERE+RN+>+1|code-block|syntax|javascript|3088404|生成的查询计划完全相同。我希望在所有情况下，查询优化器都会提出相同的计划，至少在用内联视图简单地替换CTE或反之亦然。|3088405|当然，在您自己的系统上尝试您自己的查询，看看是否有区别。|3088406|此外，where子句中的row_number()是堆栈溢出回答中的常见错误。在处理select子句之前，Logicaly+row_number()不可用。人们忘记了这一点，当他们在没有测试答案的情况下回答时，答案有时是错误的。(这是我自己犯下的一个罪名。)|offset|length|style|CODE|3088407|entityMap^0|0|0|0|0|C|C|1P|C|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]|$M|Z|N|10|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

In response to comments on rexem's answer, with respect to whether a an inline view or CTE would be faster I recast the queries to use a table I, and everyone, had available: sys.objects.

<pre><code>WITH object_rows AS (
 SELECT object_id, 
 ROW_NUMBER() OVER ( ORDER BY object_id) RN
 FROM sys.objects)
SELECT object_id
FROM object_rows
WHERE RN &gt; 1

SELECT object_id
FROM (SELECT object_id, 
 ROW_NUMBER() OVER ( ORDER BY object_id) RN
 FROM sys.objects) T
WHERE RN &gt; 1
</code></pre>

The query plans produced were exactly the same. I would expect in all cases, the query optimizer would come up with the same plan, at least in simple replacement of CTE with inline view or vice versa.

Of course, try your own queries on your own system to see if there is a difference.

Also, <code>row_number()</code> in the where clause is a common error in answers given on Stack Overflow. Logicaly <code>row_number()</code> is not available until the select clause is processed. People forget that and when they answer without testing the answer, the answer is sometimes wrong. (A charge I have myself been guilty of.)

blocks|key|3301385|text|我觉得所有显示使用CTE或Sub查询的答案都足以解决这个问题，但我看不到任何人深入了解为什么OP会有问题。OP建议的不起作用的原因是由于这里的逻辑查询处理顺序：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3301386|3301387|blockquote|3301388|3301389|3301390|FROM|ordered-list-item|3301391|ON|3301392|JOIN|3301393|WHERE|3301394|GROUP作者:使用CUBE/ROLLUP|3301395|HAVING|3301396|SELECT|3301397|DISTINCT|3301398|ORDER+BY|3301399|TOP|3301400|OFFSET/FETCH|3301401|的|3301402|unordered-list-item|3301403|3301404|我相信这对答案有很大的帮助，因为它解释了为什么会发生这样的问题。WHERE总是在SELECT之前被处理，这使得CTE或Sub查询对于许多函数来说是必要的。您将在SQL+Server中经常看到这一点。|offset|length|style|CODE|3301405|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|W|5|14|6|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|C|3|-4|5|D|7|1I|8|@]|9|@]|A|$]]|$1|E|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|1L|8|@]|9|@]|A|$]]|$1|J|3|K|5|I|7|1M|8|@]|9|@]|A|$]]|$1|L|3|M|5|I|7|1N|8|@]|9|@]|A|$]]|$1|N|3|O|5|I|7|1O|8|@]|9|@]|A|$]]|$1|P|3|Q|5|I|7|1P|8|@]|9|@]|A|$]]|$1|R|3|S|5|I|7|1Q|8|@]|9|@]|A|$]]|$1|T|3|U|5|I|7|1R|8|@]|9|@]|A|$]]|$1|V|3|W|5|I|7|1S|8|@]|9|@]|A|$]]|$1|X|3|Y|5|I|7|1T|8|@]|9|@]|A|$]]|$1|Z|3|10|5|I|7|1U|8|@]|9|@]|A|$]]|$1|11|3|12|5|I|7|1V|8|@]|9|@]|A|$]]|$1|13|3|14|5|6|7|1W|8|@]|9|@]|A|$]]|$1|15|3|-4|5|16|7|1X|8|@]|9|@]|A|$]]|$1|17|3|-4|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|18|3|19|5|6|7|1Z|8|@$1A|20|1B|21|1C|1D]|$1A|22|1B|23|1C|1D]]|9|@]|A|$]]|$1|1E|3|-4|5|6|7|24|8|@]|9|@]|A|$]]]|1F|$]]

I feel like all the answers showing use of a CTE or Sub Query are sufficient fixes for this, but I don't see anyone getting to the heart of why OP has a problem. The reason why what OP suggested doesn't work is due to logical query processing order here:

<blockquote>
 <ol>
 <li>FROM</li>
 <li>ON</li>
 <li>JOIN</li>
 <li>WHERE</li>
 <li>GROUP BY</li>
 <li>WITH CUBE/ROLLUP</li>
 <li>HAVING</li>
 <li>SELECT</li>
 <li>DISTINCT</li>
 <li>ORDER BY</li>
 <li>TOP</li>
 <li>OFFSET/FETCH</li>
 </ol>
</blockquote>

I believe this contributes to the answer greatly, because it explains why issues like this one occur. <code>WHERE</code> is always processed before <code>SELECT</code> making a CTE or Sub Query necessary for many functions. You will see this a lot in SQL Server.

blocks|key|328003|text|使用CTE+(SQL+Server+2005%2B)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|328004|WITH+employee_rows+AS+(
++SELECT+t.employee_id,
+++++++++ROW_NUMBER()+OVER+(+ORDER+BY+t.employee_id+)+'rownum'
++++FROM+V_EMPLOYEE+t)
SELECT+er.employee_id
++FROM+employee_rows+er
+WHERE+er.rownum+>+1|code-block|syntax|javascript|328005|使用内联视图/非CTE等效替代：|328006|SELECT+er.employee_id
++FROM+(SELECT+t.employee_id,
+++++++++++++++ROW_NUMBER()+OVER+(+ORDER+BY+t.employee_id+)+'rownum'
++++++++++FROM+V_EMPLOYEE+t)+er
+WHERE+er.rownum+>+1|328007|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Using CTE (SQL Server 2005+):

<pre><code>WITH employee_rows AS (
 SELECT t.employee_id,
 ROW_NUMBER() OVER ( ORDER BY t.employee_id ) 'rownum'
 FROM V_EMPLOYEE t)
SELECT er.employee_id
 FROM employee_rows er
 WHERE er.rownum &gt; 1
</code></pre>

Using Inline view/Non-CTE Equivalent Alternative:

<pre><code>SELECT er.employee_id
 FROM (SELECT t.employee_id,
 ROW_NUMBER() OVER ( ORDER BY t.employee_id ) 'rownum'
 FROM V_EMPLOYEE t) er
 WHERE er.rownum &gt; 1
</code></pre>

blocks|key|330674|text|WITH+MyCte+AS+
(
++++select+
+++++++employee_id,
+++++++RowNum+=+row_number()+OVER+(order+by+employee_id)
++++from+V_EMPLOYEE+
)
SELECT++employee_id
FROM++++MyCte
WHERE+++RowNum+>+0
ORDER+BY+employee_id|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|330675|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre class="lang-sql prettyprint-override"><code>WITH MyCte AS 
(
 select 
 employee_id,
 RowNum = row_number() OVER (order by employee_id)
 from V_EMPLOYEE 
)
SELECT employee_id
FROM MyCte
WHERE RowNum &gt; 0
ORDER BY employee_id
</code></pre>

blocks|key|329506|text|根据OP对问题的回答：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|329507|329508|请查看此链接。它有一个不同的解决方案，看起来对提出问题的人有效。我正在试着找出这样的解决方案。|blockquote|329509|329510|Paginated+query+using+sorting+on+different+columns+using+ROW_NUMBER()+OVER+()+in+SQL+Server+2005|offset|length|329511|~约瑟夫|329512|329513|“方法1”类似于OP来自链接问题的查询，而“方法2”类似于来自所选答案的查询。您必须查看此answer中链接的代码才能了解真正发生了什么，因为所选答案中的代码已被修改以使其工作。试试这个：|329514|DECLARE+@YourTable+table+(RowID+int+not+null+primary+key+identity,+Value1+int,+Value2+int,+value3+int)
SET+NOCOUNT+ON
INSERT+INTO+@YourTable+VALUES+(1,1,1)
INSERT+INTO+@YourTable+VALUES+(1,1,2)
INSERT+INTO+@YourTable+VALUES+(1,1,3)
INSERT+INTO+@YourTable+VALUES+(1,2,1)
INSERT+INTO+@YourTable+VALUES+(1,2,2)
INSERT+INTO+@YourTable+VALUES+(1,2,3)
INSERT+INTO+@YourTable+VALUES+(1,3,1)
INSERT+INTO+@YourTable+VALUES+(1,3,2)
INSERT+INTO+@YourTable+VALUES+(1,3,3)
INSERT+INTO+@YourTable+VALUES+(2,1,1)
INSERT+INTO+@YourTable+VALUES+(2,1,2)
INSERT+INTO+@YourTable+VALUES+(2,1,3)
INSERT+INTO+@YourTable+VALUES+(2,2,1)
INSERT+INTO+@YourTable+VALUES+(2,2,2)
INSERT+INTO+@YourTable+VALUES+(2,2,3)
INSERT+INTO+@YourTable+VALUES+(2,3,1)
INSERT+INTO+@YourTable+VALUES+(2,3,2)
INSERT+INTO+@YourTable+VALUES+(2,3,3)
INSERT+INTO+@YourTable+VALUES+(3,1,1)
INSERT+INTO+@YourTable+VALUES+(3,1,2)
INSERT+INTO+@YourTable+VALUES+(3,1,3)
INSERT+INTO+@YourTable+VALUES+(3,2,1)
INSERT+INTO+@YourTable+VALUES+(3,2,2)
INSERT+INTO+@YourTable+VALUES+(3,2,3)
INSERT+INTO+@YourTable+VALUES+(3,3,1)
INSERT+INTO+@YourTable+VALUES+(3,3,2)
INSERT+INTO+@YourTable+VALUES+(3,3,3)
SET+NOCOUNT+OFF

DECLARE+@PageNumber+++++int
DECLARE+@PageSize+++++++int
DECLARE+@SortBy+++++++++int

SET+@PageNumber=3
SET+@PageSize=5
SET+@SortBy=1


--SELECT+*+FROM+@YourTable

--Method+1
;WITH+PaginatedYourTable+AS+(
SELECT
++++RowID,Value1,Value2,Value3
++++++++,CASE+@SortBy
+++++++++++++WHEN++1+THEN+ROW_NUMBER()+OVER+(ORDER+BY+Value1+ASC)
+++++++++++++WHEN++2+THEN+ROW_NUMBER()+OVER+(ORDER+BY+Value2+ASC)
+++++++++++++WHEN++3+THEN+ROW_NUMBER()+OVER+(ORDER+BY+Value3+ASC)
+++++++++++++WHEN+-1+THEN+ROW_NUMBER()+OVER+(ORDER+BY+Value1+DESC)
+++++++++++++WHEN+-2+THEN+ROW_NUMBER()+OVER+(ORDER+BY+Value2+DESC)
+++++++++++++WHEN+-3+THEN+ROW_NUMBER()+OVER+(ORDER+BY+Value3+DESC)
+++++++++END+AS+RowNumber
++++FROM+@YourTable
++++--WHERE
)
SELECT
++++RowID,Value1,Value2,Value3,RowNumber
++++++++,@PageNumber+AS+PageNumber,+@PageSize+AS+PageSize,+@SortBy+AS+SortBy
++++FROM+PaginatedYourTable
++++WHERE+RowNumber>=(@PageNumber-1)*@PageSize+AND+RowNumber<=(@PageNumber*@PageSize)-1
++++ORDER+BY+RowNumber



--------------------------------------------
--Method+2
;WITH+PaginatedYourTable+AS+(
SELECT
++++RowID,Value1,Value2,Value3
++++++++,ROW_NUMBER()+OVER
+++++++++(
+++++++++++++ORDER+BY
+++++++++++++++++CASE+@SortBy
+++++++++++++++++++++WHEN++1+THEN+Value1
+++++++++++++++++++++WHEN++2+THEN+Value2
+++++++++++++++++++++WHEN++3+THEN+Value3
+++++++++++++++++END+ASC
++++++++++++++++,CASE+@SortBy
+++++++++++++++++++++WHEN+-1+THEN+Value1
+++++++++++++++++++++WHEN+-2+THEN+Value2
+++++++++++++++++++++WHEN+-3+THEN+Value3
+++++++++++++++++END+DESC
+++++++++)+RowNumber
++++FROM+@YourTable
++++--WHERE++more+conditions+here
)
SELECT
++++RowID,Value1,Value2,Value3,RowNumber
++++++++,@PageNumber+AS+PageNumber,+@PageSize+AS+PageSize,+@SortBy+AS+SortBy
++++FROM+PaginatedYourTable
++++WHERE+
++++++++RowNumber>=(@PageNumber-1)*@PageSize+AND+RowNumber<=(@PageNumber*@PageSize)-1
++++++++--AND+more+conditions+here
++++ORDER+BY
++++++++CASE+@SortBy
++++++++++++WHEN++1+THEN+Value1
++++++++++++WHEN++2+THEN+Value2
++++++++++++WHEN++3+THEN+Value3
++++++++END+ASC
+++++++,CASE+@SortBy
++++++++++++WHEN+-1+THEN+Value1
++++++++++++WHEN+-2+THEN+Value2
++++++++++++WHEN+-3+THEN+Value3
++++++++END+DESC|code-block|syntax|javascript|329515|输出：|329516|RowID++Value1+Value2+Value3+RowNumber++PageNumber++PageSize++++SortBy
------+------+------+------+----------+-----------+-----------+-----------
10+++++2++++++1++++++1++++++10+++++++++3+++++++++++5+++++++++++1
11+++++2++++++1++++++2++++++11+++++++++3+++++++++++5+++++++++++1
12+++++2++++++1++++++3++++++12+++++++++3+++++++++++5+++++++++++1
13+++++2++++++2++++++1++++++13+++++++++3+++++++++++5+++++++++++1
14+++++2++++++2++++++2++++++14+++++++++3+++++++++++5+++++++++++1

(5+row(s)+affected

RowID++Value1+Value2+Value3+RowNumber++PageNumber++PageSize++++SortBy
------+------+------+------+----------+-----------+-----------+-----------
10+++++2++++++1++++++1++++++10+++++++++3+++++++++++5+++++++++++1
11+++++2++++++1++++++2++++++11+++++++++3+++++++++++5+++++++++++1
12+++++2++++++1++++++3++++++12+++++++++3+++++++++++5+++++++++++1
13+++++2++++++2++++++1++++++13+++++++++3+++++++++++5+++++++++++1
14+++++2++++++2++++++2++++++14+++++++++3+++++++++++5+++++++++++1

(5+row(s)+affected)|329517|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/230058/paginated-query-using-sorting-on-different-columns-using-rownumber-over-in|1|https://stackoverflow.com/questions/230058/paginated-query-using-sorting-on-different-columns-using-rownumber-over-in/240108#240108^0|0|0|0|0|0|2O|0|0|0|0|19|6|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1A|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|1C|8|@]|9|@$I|1D|J|1E|1|1F]]|A|$]]|$1|K|3|L|5|6|7|1G|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1I|8|@]|9|@$I|1J|J|1K|1|1L]]|A|$]]|$1|P|3|Q|5|R|7|1M|8|@]|9|@]|A|$S|T]]|$1|U|3|V|5|6|7|1N|8|@]|9|@]|A|$]]|$1|W|3|X|5|R|7|1O|8|@]|9|@]|A|$S|T]]|$1|Y|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]|16|$5|11|12|13|A|$14|17]]]]

based on OP's answer to question:

<blockquote>
 Please see this link. Its having a
 different solution, which looks
 working for the person who asked the
 question. I'm trying to figure out a
 solution like this.
 
 <a href="https://stackoverflow.com/questions/230058/paginated-query-using-sorting-on-different-columns-using-rownumber-over-in">Paginated query using sorting on different columns using ROW_NUMBER() OVER () in SQL Server 2005</a>
 
 ~Joseph
</blockquote>

"method 1" is like the OP's query from the linked question, and "method 2" is like the query from the selected answer. You had to look at the code linked in this <a href="https://stackoverflow.com/questions/230058/paginated-query-using-sorting-on-different-columns-using-rownumber-over-in/240108#240108">answer</a> to see what was really going on, since the code in the selected answer was modified to make it work. Try this:

<pre><code>DECLARE @YourTable table (RowID int not null primary key identity, Value1 int, Value2 int, value3 int)
SET NOCOUNT ON
INSERT INTO @YourTable VALUES (1,1,1)
INSERT INTO @YourTable VALUES (1,1,2)
INSERT INTO @YourTable VALUES (1,1,3)
INSERT INTO @YourTable VALUES (1,2,1)
INSERT INTO @YourTable VALUES (1,2,2)
INSERT INTO @YourTable VALUES (1,2,3)
INSERT INTO @YourTable VALUES (1,3,1)
INSERT INTO @YourTable VALUES (1,3,2)
INSERT INTO @YourTable VALUES (1,3,3)
INSERT INTO @YourTable VALUES (2,1,1)
INSERT INTO @YourTable VALUES (2,1,2)
INSERT INTO @YourTable VALUES (2,1,3)
INSERT INTO @YourTable VALUES (2,2,1)
INSERT INTO @YourTable VALUES (2,2,2)
INSERT INTO @YourTable VALUES (2,2,3)
INSERT INTO @YourTable VALUES (2,3,1)
INSERT INTO @YourTable VALUES (2,3,2)
INSERT INTO @YourTable VALUES (2,3,3)
INSERT INTO @YourTable VALUES (3,1,1)
INSERT INTO @YourTable VALUES (3,1,2)
INSERT INTO @YourTable VALUES (3,1,3)
INSERT INTO @YourTable VALUES (3,2,1)
INSERT INTO @YourTable VALUES (3,2,2)
INSERT INTO @YourTable VALUES (3,2,3)
INSERT INTO @YourTable VALUES (3,3,1)
INSERT INTO @YourTable VALUES (3,3,2)
INSERT INTO @YourTable VALUES (3,3,3)
SET NOCOUNT OFF

DECLARE @PageNumber int
DECLARE @PageSize int
DECLARE @SortBy int

SET @PageNumber=3
SET @PageSize=5
SET @SortBy=1


--SELECT * FROM @YourTable

--Method 1
;WITH PaginatedYourTable AS (
SELECT
 RowID,Value1,Value2,Value3
 ,CASE @SortBy
 WHEN 1 THEN ROW_NUMBER() OVER (ORDER BY Value1 ASC)
 WHEN 2 THEN ROW_NUMBER() OVER (ORDER BY Value2 ASC)
 WHEN 3 THEN ROW_NUMBER() OVER (ORDER BY Value3 ASC)
 WHEN -1 THEN ROW_NUMBER() OVER (ORDER BY Value1 DESC)
 WHEN -2 THEN ROW_NUMBER() OVER (ORDER BY Value2 DESC)
 WHEN -3 THEN ROW_NUMBER() OVER (ORDER BY Value3 DESC)
 END AS RowNumber
 FROM @YourTable
 --WHERE
)
SELECT
 RowID,Value1,Value2,Value3,RowNumber
 ,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
 FROM PaginatedYourTable
 WHERE RowNumber&gt;=(@PageNumber-1)*@PageSize AND RowNumber&lt;=(@PageNumber*@PageSize)-1
 ORDER BY RowNumber



--------------------------------------------
--Method 2
;WITH PaginatedYourTable AS (
SELECT
 RowID,Value1,Value2,Value3
 ,ROW_NUMBER() OVER
 (
 ORDER BY
 CASE @SortBy
 WHEN 1 THEN Value1
 WHEN 2 THEN Value2
 WHEN 3 THEN Value3
 END ASC
 ,CASE @SortBy
 WHEN -1 THEN Value1
 WHEN -2 THEN Value2
 WHEN -3 THEN Value3
 END DESC
 ) RowNumber
 FROM @YourTable
 --WHERE more conditions here
)
SELECT
 RowID,Value1,Value2,Value3,RowNumber
 ,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
 FROM PaginatedYourTable
 WHERE 
 RowNumber&gt;=(@PageNumber-1)*@PageSize AND RowNumber&lt;=(@PageNumber*@PageSize)-1
 --AND more conditions here
 ORDER BY
 CASE @SortBy
 WHEN 1 THEN Value1
 WHEN 2 THEN Value2
 WHEN 3 THEN Value3
 END ASC
 ,CASE @SortBy
 WHEN -1 THEN Value1
 WHEN -2 THEN Value2
 WHEN -3 THEN Value3
 END DESC
</code></pre>

OUTPUT:

<pre><code>RowID Value1 Value2 Value3 RowNumber PageNumber PageSize SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10 2 1 1 10 3 5 1
11 2 1 2 11 3 5 1
12 2 1 3 12 3 5 1
13 2 2 1 13 3 5 1
14 2 2 2 14 3 5 1

(5 row(s) affected

RowID Value1 Value2 Value3 RowNumber PageNumber PageSize SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10 2 1 1 10 3 5 1
11 2 1 2 11 3 5 1
12 2 1 3 12 3 5 1
13 2 2 1 13 3 5 1
14 2 2 2 14 3 5 1

(5 row(s) affected)
</code></pre>

blocks|key|329522|text|+select+salary+from+(
+select++Salary,+ROW_NUMBER()+over+(order+by+Salary+desc)+rn+from+Employee+
+)+t+where+t.rn+=+2|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|329523|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> select salary from (
 select Salary, ROW_NUMBER() over (order by Salary desc) rn from Employee 
 ) t where t.rn = 2
</code></pre>

I found one question answered with the <code>Row_Number()</code> function in the where clause. When I tried one query, I was getting the following error:

<blockquote>
 "Msg 4108, Level 15, State 1, Line 1 
 Windowed functions can only appear in the SELECT or ORDER BY clauses."
</blockquote>

Here is the query I tried. If somebody knows how to solve this, please let me know.

<pre><code>SELECT employee_id 
FROM V_EMPLOYEE 
WHERE row_number() OVER ( ORDER BY employee_id ) &gt; 0 
ORDER BY Employee_ID
</code></pre>

SQL Row_Number() function in Where Clause

SQL Server

我在where子句中发现了一个用Row_Number()函数回答的问题。当我尝试一个查询时，我得到了以下错误：“消息4108，级别15，状态1，行1窗口函数只能出现在SELECT或ORDER BY子句中。”下面是我尝试过的查询。如果有人知道如何解决这个问题，请让我知道。SELECT employee_id FROM V_EMPLOYEE WHERE row_number() OVER ( ORDE

问Where子句中的SQL Row_Number()函数
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Where子句中的SQL Row_Number()函数EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Where子句中的SQL Row_Number()函数
EN