我正在尝试添加一列，为每条记录分配行号
我对代码进行了更改，添加了所需的列
但我得到了重复的行号。如何获得唯一的行号？.
**Note: This code can be executed in the SQL editor like that. It needs no sample data**
select distinct trunc(GenerateTimeBy1Day,'day') as claim_eff_date, trunc(GenerateTimeBy1DayPlus20,'day') as bwe_to_completeby from
(
select from_dt + (level - 1)/1 as GenerateTimeBy1Day, (from_dt + (level - 1)/1) + 20 as GenerateTimeBy1DayPlus20
from (select from_dt
,to_dt
,to_dt - from_dt + 1 as days_between
from (select to_date('03-Jan-2021') as from_dt
, to_date('30-Jan-2021') as to_dt
from dual))
connect by (level - 1) <= days_between
)
order by claim_eff_date
我对代码进行了这些更改，以添加所需的列
**Note: This code can be executed in the SQL editor like that. It needs no sample data**
select distinct trunc(GenerateTimeBy1Day,'day') as claim_eff_date, trunc(GenerateTimeBy1DayPlus20,'day') as bwe_to_completeby, row_number()
over (PARTITION BY trunc(GenerateTimeBy1Day,'day'), trunc(GenerateTimeBy1DayPlus20,'day') ORDER BY trunc(GenerateTimeBy1Day,'day')) as row_number from
(
select from_dt + (level - 1)/1 as GenerateTimeBy1Day, (from_dt + (level - 1)/1) + 20 as GenerateTimeBy1DayPlus20
from (select from_dt
,to_dt
,to_dt - from_dt + 1 as days_between
from (select to_date('03-Jan-2021') as from_dt
, to_date('30-Jan-2021') as to_dt
from dual))
connect by (level - 1) <= days_between
)
order by claim_eff_date
但我得到
Row_Number
----------
1
1
2
3
4
5
6
1
1
2
3
如何获得唯一的行号？
Row_Number
----------
1
2
3
4
5
6
7
8
9
10
我不确定这些日期是否代表，但是-如果您最初编写的查询完成了任务，那么将其用作计算行号的最终查询的源：
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
SQL> with your_query as (
2 select distinct trunc(GenerateTimeBy1Day,'day') as claim_eff_date,
3 trunc(GenerateTimeBy1DayPlus20,'day') as bwe_to_completeby
4 from
5 (
6 select from_dt + (level - 1)/1 as GenerateTimeBy1Day, (from_dt + (level - 1)/1) + 20 as GenerateTimeBy1DayPlus20
7 from (select from_dt
8 ,to_dt
9 ,to_dt - from_dt + 1 as days_between
10 from (select date '2021-01-03' as from_dt
11 , date '2021-01-30' as to_dt
12 from dual))
13 connect by (level - 1) <= days_between
14 ))
15 select claim_eff_date,
16 bwe_to_completeby,
17 row_number() over (order by claim_eff_date) rn
18 from your_query
19 order by claim_eff_date;
CLAIM_EFF_ BWE_TO_COM RN
---------- ---------- ----------
28.12.2020 18.01.2021 1
04.01.2021 25.01.2021 2
04.01.2021 18.01.2021 3
11.01.2021 01.02.2021 4
11.01.2021 25.01.2021 5
18.01.2021 08.02.2021 6
18.01.2021 01.02.2021 7
25.01.2021 08.02.2021 8
25.01.2021 15.02.2021 9
9 rows selected.
SQL>
顺便说一句，这就是你写的：to_date('03-Jan-2021')。这是错误的。应用于不带日期格式掩码的字符串的TO_DATE依赖于Oracle的能力来猜测您的意思。此外，这甚至在我的数据库中都不起作用，尽管你说
此代码可以在SQL编辑器中执行
我的数据库讲克罗地亚语，没有一个月能像Jan那样。
更安全的选择是
- 应用格式掩码：
to_date('03-Jan-2021', 'dd-mon-yyyy', 'nls_date_language = english') - 或者，使用日期文字(就像我做的那样(：
date '2021-01-03'，它总是有date关键字和yyyy-mm-dd格式的日期，所以没有歧义