用以前的行值填充表中的行oracle sql

Question

+----+---------+-------+--------+---------+--------+
| id | counter | name  | duties | remarks | monies |
+----+---------+-------+--------+---------+--------+
|  1 |       1 | jake  |        |         |        |
|  2 |       0 |       | clean  | misc    | 12k    |
|  3 |       1 | james |        |         |        |
|  4 |       0 |       | clean  | misc    | 12k    |
|  5 |       0 |       | soap   | misc    | 12k    |
|  6 |       0 |       | shower | misc    | 12k    |
|  7 |       1 | john  |        |         |        |
|  8 |       0 |       | dry    | misc    | 12k    |
|  9 |       0 |       | scrub  | misc    | 12k    |
+----+---------+-------+--------+---------+--------+

上面的数据是按人员分组的，然后列出了他们的职责：

我需要一种使用oracle sql让数据看起来像这样的方法：

+----+---------+-------+--------+---------+--------+
| id | counter | name  | duties | remarks | monies |
+----+---------+-------+--------+---------+--------+
|  1 |       1 | jake  |        |         |        |
|  2 |       0 | jake  | clean  | misc    | 12k    |
|  3 |       1 | james |        |         |        |
|  4 |       0 | james | clean  | misc    | 12k    |
|  5 |       0 | james | soap   | misc    | 12k    |
|  6 |       0 | james | shower | misc    | 12k    |
|  7 |       1 | john  |        |         |        |
|  8 |       0 | john  | dry    | misc    | 12k    |
|  9 |       0 | john  | scrub  | misc    | 12k    |
+----+---------+-------+--------+---------+--------+

在迭代每一行时遇到一点麻烦……不介意它的plsql - sql优先于

尝试了一些东西..。但是对于每个人，它在第二行之后返回null

Answer 1

一种方法是lag(ignore nulls)

select coalesce(name, lag(name ignore nulls) over (order by id)),
       . . .
from t;

这是从name列中获取前一个非NULL值，其中" previous“基于id列的顺序。