SQL - 过滤后的顺序
如何在 TSQL (SQL Server) 中对数据进行排序并然后过滤它?
我尝试过这样的事情:
SELECT [Job].*,
ROW_NUMBER() OVER (ORDER BY [Job].[Date]) AS RowNum
FROM [Job]
ORDER BY Rank
WHERE RowNum >= @Start AND RowNum < @End
不起作用。我还尝试使用子查询,它会抛出:
ORDER BY 子句在以下情况下无效 视图、内联函数、派生函数 表、子查询和公用表 表达式,除非 TOP 或 FOR XML 是 还指定了。
我不想使用 TOP 或 FOR XML。
怎么解决这个问题呢?
How can I order the data and then filter it in TSQL (SQL Server)?
I've tried something like this:
SELECT [Job].*,
ROW_NUMBER() OVER (ORDER BY [Job].[Date]) AS RowNum
FROM [Job]
ORDER BY Rank
WHERE RowNum >= @Start AND RowNum < @End
Doesn't work. I also tried to use a subquery, which throws:
The ORDER BY clause is invalid in
views, inline functions, derived
tables, subqueries, and common table
expressions, unless TOP or FOR XML is
also specified.
I don't want to use TOP or FOR XML.
How to solve this?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
使用 CTE。请注意,本例中的“内部”ORDER BY 由 ROW_NUMBER/OVER 隐含。
编辑:
@Start 和 @End 之间的搜索是按日期 ROW_NUMBER 生成的序列进行的。
排名与此顺序无关。排名(假设它是表中的一列)将被忽略,因为您的序列是在日期上。你不需要对它进行排序。
如果“rank”实际上是“RowNum”,那么您仍然不需要“内部”排序,因为它是一个集合操作。不过,您将在最外面的排序中需要它。
如果排名是日期的次要排序,则使用以下命令:
Use a CTE. Note, the "inner" ORDER BY in this case is implied by the ROW_NUMBER/OVER.
Edit:
Your search between @Start and @End is on the sequence generated by the ROW_NUMBER on date.
Rank has no relation to this sequence. Rank (assuming it's a column in the table) will be ignored because your sequence is on Date. You don't need to sort on it.
If "rank" is actually "RowNum" then you still don't need an "inner" sort because it's a set operation. You'll need it on the outermost sort though.
If rank is a secondary sort on Date then use this: