sql—使用partition by子句时如何排序

q5iwbnjs 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(270)

我想给数据按日期排序的每一行分配一个行号，当列“project\u name”更改和列“ok\u criterium”更改时，我想从0开始重新分配。
ddl地址：

CREATE TABLE TEST1 
   ("PROJECT_NAME" NUMBER, 
    "OK_CRITERIUM" NUMBER, 
    "DDATE" DATE
   ) ;

数据：

Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','1',to_date('21.12.2019 00:00:00','DD.MM.RRRR HH24:MI:SS'));
Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','1',to_date('21.12.2019 00:00:00','DD.MM.RRRR HH24:MI:SS'));
Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','0',to_date('26.12.2019 00:00:00','DD.MM.RRRR HH24:MI:SS'));
Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','0',to_date('26.12.2019 00:00:00','DD.MM.RRRR HH24:MI:SS'));
Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','1',to_date('01.01.2020 00:00:00','DD.MM.RRRR HH24:MI:SS'));
Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','0',to_date('01.01.2020 00:00:00','DD.MM.RRRR HH24:MI:SS'));

我的选择：

select 
row_number () over (partition by project_name,ok_criterium order by ddate asc ) as rn,
t1.*
from test1 t1

结果：

RN PROJECT_NAME OK_CRITERIUM DDATE 
1   241 0   26.12.2019 00:00:00
2   241 0   26.12.2019 00:00:00
3   241 0   01.01.2020 00:00:00
1   241 1   21.12.2019 00:00:00
2   241 1   21.12.2019 00:00:00
3   241 1   01.01.2020 00:00:00

问题：行未按列ddate排序。您可以在日期26.12.2019之后看到日期21.12.2019。数据也是按“确定标准”排序的，我不需要。
我需要的是：

RN PROJECT_NAME OK_CRITERIUM DDATE 
1 241   1   21.12.2019 00:00:00
2 241   1   21.12.2019 00:00:00
1 241   0   26.12.2019 00:00:00
2 241   0   26.12.2019 00:00:00
1 241   1   01.01.2020 00:00:00
1 241   0   01.01.2020 00:00:00

sql oracle

来源：https://stackoverflow.com/questions/62864376/how-to-order-when-using-partition-by-clause

2条答案

按热度按时间

4ioopgfo1#

请使用下面的查询。你也得按日期划分。

select 
row_number () over (partition by project_name,ok_criterium, ddate order by ddate asc ) 
as rn,
t1.*
from test1 t1 order by ddate;

下面是演示，
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e08000697ced91ee3e1df392ba0d8ac9

赞(0）回复(0）举报 2021-07-26

oalqel3c2#

考虑到您的需求，您应该在分区子句中包含ddate。您还需要使用ddate对结果进行排序。

select t2. from (select 
row_number () over (partition by project_name,ok_criterium, ddate order by ddate asc) as rn,
t1.*
from test1 t1) t2 order by ddate asc;

赞(0）回复(0）举报 2021-07-26

我来回答

sql—使用partition by子句时如何排序

2条答案

相关问题

热门标签

最新问答