Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TX(
A varchar2(50),
B varchar2(50),
C Nvarchar2(50),
D number(10)
);
INSERT INTO TX (A,B,C,D)
VALUES ('2021/2/1','adidas','鞋子',325);
1 rows affected

INSERT INTO TX (A,B,C,D)
VALUES ('2021/4/1','adidas','鞋子',325);
1 rows affected

INSERT INTO TX (A,B,C,D)
VALUES ('2021/5/1','adidas','鞋子',316);
1 rows affected

INSERT INTO TX (A,B,C,D)
VALUES ('2021/7/1','adidas','鞋子',325);
1 rows affected

INSERT INTO TX (A,B,C,D)
VALUES ('2021/7/1','nike','鞋子',325);
1 rows affected

SELECT *
FROM TX;


A
B
C
D


2021/2/1
adidas
鞋子
325

2021/4/1
adidas
鞋子
325

2021/5/1
adidas
鞋子
316

2021/7/1
adidas
鞋子
325

2021/7/1
nike
鞋子
325


CREATE TABLE TY(
A varchar2(50));
INSERT INTO TY (A)
VALUES ('2021/1/1');
1 rows affected

INSERT INTO TY (A)
VALUES ('2021/2/1');
1 rows affected

INSERT INTO TY (A)
VALUES ('2021/3/1');
1 rows affected

INSERT INTO TY (A)
VALUES ('2021/4/1');
1 rows affected

INSERT INTO TY (A)
VALUES ('2021/5/1');
1 rows affected

INSERT INTO TY (A)
VALUES ('2021/6/1');
1 rows affected

INSERT INTO TY (A)
VALUES ('2021/7/1');
1 rows affected

SELECT *
FROM TY;


A


2021/1/1

2021/2/1

2021/3/1

2021/4/1

2021/5/1

2021/6/1

2021/7/1


SELECT TY.A,
LAST_VALUE(TX.B IGNORE NULLS) OVER (ORDER BY TY.A,TX.B,TX.C) AS B,
LAST_VALUE(TX.C IGNORE NULLS) OVER (ORDER BY TY.A,TX.B,TX.C) AS C,
LAST_VALUE(TX.D IGNORE NULLS) OVER (ORDER BY TY.A,TX.B,TX.C) AS D
FROM TY
LEFT JOIN TX ON TX.A=TY.A
ORDER BY TY.A,TX.B,TX.C


A
B
C
D


2021/1/1
null
null
null

2021/2/1
adidas
鞋子
325

2021/3/1
adidas
鞋子
325

2021/4/1
adidas
鞋子
325

2021/5/1
adidas
鞋子
316

2021/6/1
adidas
鞋子
316

2021/7/1
adidas
鞋子
325

2021/7/1
nike
鞋子
325

A	B	C	D
2021/2/1	adidas	鞋子	325
2021/4/1	adidas	鞋子	325
2021/5/1	adidas	鞋子	316
2021/7/1	adidas	鞋子	325
2021/7/1	nike	鞋子	325

A	B	C	D
2021/1/1	null	null	null
2021/2/1	adidas	鞋子	325
2021/3/1	adidas	鞋子	325
2021/4/1	adidas	鞋子	325
2021/5/1	adidas	鞋子	316
2021/6/1	adidas	鞋子	316
2021/7/1	adidas	鞋子	325
2021/7/1	nike	鞋子	325