你可以尝试使用MAX窗口函数来实现它.
CREATE TABLE T(
ID int,QTR int,SCORE int
);
insert into t values (21,1,3);
insert into t values (21,2,5);
insert into t values (21,3,4,3);
insert into t values (41,2);
insert into t values (41,4);
insert into t values (41,2);
查询1:
SELECT t1.ID,t1.QTR,max(SCORE) over(partition by ID order by QTR) SCORE
FROM T t1
Results:
| ID | QTR | SCORE |
|----|-----|-------|
| 21 | 1 | 3 |
| 21 | 2 | 5 |
| 21 | 3 | 5 |
| 21 | 4 | 5 |
| 41 | 1 | 2 |
| 41 | 2 | 2 |
| 41 | 3 | 4 |
| 41 | 4 | 4 |