场景:
- TABLE1表中记录不应可用,记录标记为“A”
1.如果表1中的loan_no可用,则记录的标志应为“D”,表1中可用的最大记录的日期应小于2023年3月1日
表1:
loan_no flag amt dt
913813 A 100 01-01-2023
913813 A 200 02-01-2023
913813 D 300 03-01-2023
813713 D 600 04-01-2023
813713 D 8700 05-01-2023
813713 D 560 06-01-2023
123678 D 150 07-01-2023
567345 P 789 08-01-2023
567345 D 789 09-01-2023
912353 D 454 10-09-2023
预期结果:
loan_no flag amt dt
813713 D 560 06-01-2023
123678 D 150 07-01-2023
查询应该在select子句中,因为我要将此查询连接到另外三个表。因此,成本不应因性能而变高。
下面是XML和DML:
create table TABLE1 (
loan_no varchar2(200),
flag varchar2(200),
amt varchar2(200),
dt date
);
/
insert into table1 values ('913813','A','100',to_date('01-01-2023','dd-mm-yyyy'));
insert into table1 values ('913813','A','200',to_date('02-01-2023','dd-mm-yyyy'));
insert into table1 values ('913813','D','300',to_date('03-01-2023','dd-mm-yyyy'));
insert into table1 values ('813713','D','600',to_date('04-01-2023','dd-mm-yyyy'));
insert into table1 values ('813713','D','8700',to_date('05-01-2023','dd-mm-yyyy'));
insert into table1 values ('813713','D','560',to_date('06-01-2023','dd-mm-yyyy'));
insert into table1 values ('123678','D','150',to_date('07-01-2023','dd-mm-yyyy'));
insert into table1 values ('567345','P','789',to_date('08-01-2023','dd-mm-yyyy'));
insert into table1 values ('567345','D','789',to_date('09-01-2023','dd-mm-yyyy'));
insert into table1 values ('912353','D','454',to_date('10-01-2023','dd-mm-yyyy'));
1条答案
按热度按时间vwkv1x7d1#
您可以使用分析窗口函数生成条件列,然后使用它们筛选行-
Demo.