database SQL 中没有结束的 ROW_NUMBER()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12914499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ROW_NUMBER() without over in SQL
提问by Haseeb Akhtar
Is there any way to use ROW_NUMBER()in SQL without using OVER, because I want to use sorting.
有没有什么方法可以ROW_NUMBER()在不使用的情况下在 SQL 中使用OVER，因为我想使用排序。
I have a Grid with multiple sortable columns with configurable rows. In my scenario order by is variable that's why I am not able to put order by using ROWNUM.
我有一个带有多个可排序列和可配置行的网格。在我的场景中 order by 是可变的，这就是为什么我无法使用 ROWNUM 下订单的原因。
回答by SalientBrain
select ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as number from Task order by RuleId
回答by Koen Lostrie
I approached this using a different logic. Instead of checking for every single day and verifying it is a weekday or a holiday I create a table which lists only the business days and then take the nth value.
我使用不同的逻辑来解决这个问题。我没有检查每一天并验证它是工作日还是假日，而是创建了一个仅列出工作日的表，然后取第 n 个值。
CREATE OR REPLACE FUNCTION add_n_working_days (
start_date DATE, working_days PLS_INTEGER
) RETURN DATE AS
l_end_date DATE := start_date;
l_counter pls_integer := 0;
BEGIN
SELECT
business_day
INTO l_end_date
FROM
(
WITH
dates AS
(SELECT start_date + level - 1 as dt FROM dual CONNECT BY level < 100)
,weekdates AS
(SELECT dt as weekday FROM dates WHERE TO_CHAR(dt,'fmdy') NOT IN ('sat','sun'))
,business_days AS
(
SELECT weekday as business_day FROM weekdates
MINUS
SELECT holiday FROM so_holidays
)
SELECT business_day, ROW_NUMBER() OVER (ORDER BY 1) as rn from business_days
)
WHERE rn = working_days + 1;
RETURN l_end_date;
END add_n_working_days;
If working_days gets too high then I suggest making that value 100 a variable.
如果working_days 太高，那么我建议将该值设置为 100 变量。
