+----+---------+-------+--------+---------+--------+
| 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首选tho
尝试了几件事...但是在每个人第二行之后它又返回了null
一种方法是lag(ignore nulls)
:
select coalesce(name, lag(name ignore nulls) over (order by id)),
. . .
from t;
这是NULL
从name
列中获取先前的非值,其中“ previous”是基于id
列的顺序。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句