SQL Row_Number（）函数在Where子句没有ORDER BY？ [英] SQL Row_Number() function in Where Clause without ORDER BY?

问题描述

我发现了很多关于这个主题的问题，有很好的解决方案，但没有一个实际上处理如果数据不是以一种特定的方式排序。例如，以下查询：

I found a bunch of questions on this topic with nice solutions but none of them actually deal with what to do if the data is not to be ordered in one specific way. For instance, the following query:

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

如果数据由employee_id排序，则效果很好。但是如果我的数据没有任何特定的顺序，但行号本身作为一个ID？我的目标是写一个这样的查询（ Row_Number（）函数没有 ORDER BY / p>

works well if the data is to be ordered by employee_id. But what if my data does not have any specific order but the row numbers themselves act as an ID? My goal is to write a query like this (with the Row_Number() function having no ORDER BY clause):

WITH MyCte AS 
(
    select   employee_id,
             RowNum = row_number() OVER ( <PRESERVE ORIGINAL ORDER FROM DB> )
    from     V_EMPLOYEE 
    ORDER BY Employee_ID
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0

编辑：在Google上，我发现这不是真的可能。有人可以建议一个解决方法吗？

Upon Googling, I figured out that this is not really possible. Can some suggest a workaround for this?

推荐答案

只是为了防止它对别人有用。我只是想从其他地方：

Just in case it is useful to someone else. I just figured it out from elsewhere:

WITH MyCte AS 
(
    select   employee_id,
             RowNum = row_number() OVER (ORDER BY (SELECT 0))
    from     V_EMPLOYEE 
    ORDER BY Employee_ID
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0

这篇关于SQL Row_Number（）函数在Where子句没有ORDER BY？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！