我不确定标题是否正确,但这是我的问题。我有一张这样的table:
+----+--------+--------------+---------+------------+
| id | city | province | status | date |
+----+--------+--------------+---------+------------+
| 1 | cainta | rizal | failed | 01/01/2020 |
| 1 | null | null | success | 02/01/2020 |
| 1 | cainta | rizal | failed | 03/01/2020 |
| 2 | pasig | metro manila | failed | 04/01/2020 |
| 2 | pasig | metro manila | failed | 05/01/2020 |
| 2 | null | null | success | 06/01/2020 |
| 3 | obando | bulacan | failed | 07/01/2020 |
| 3 | null | null | failed | 08/01/2020 |
| 3 | obando | bulacan | success | 09/01/2020 |
+----+--------+--------------+---------+------------+
现在我需要获取状态为“success”的所有事务。如果我这样做,输出将是这样的:
| id | city | province | status | date |
|------|--------|------------|----------|------------|
| 1 | nan | nan | success | 02/01/2020 |
| 2 | nan | nan | success | 06/01/2020 |
| 3 | obando | bulacan | success | 09/01/2020 |
我需要的是:
| id | city | province | status | date |
|------|--------|--------------|----------|------------|
| 1 | cainta | rizal | success | 02/01/2020 |
| 2 | pasig | metro manila | success | 06/01/2020 |
| 3 | obando | bulacan | success | 09/01/2020 |
希望有人能对如何处理这种情况有所帮助。
4条答案
按热度按时间lh80um4z1#
使用以下命令尝试
lag()
```with cte as
(
select
*,
lag(city) over (order by id) as ncity,
lag(province) over (order by id) as nprovince
from myTable
)
select
id,
coalesce(city, ncity) as city,
coalesce(province, nprovince) as province,
status,
date
from cte
where status = 'success';
gorkyyrv2#
也许窗口函数可以帮助:
z2acfund3#
你可以在这里使用分析函数。
由于更改了示例数据,因此可以使用
GROUP BY
具体如下:dwthyt8l4#
如果每个id只需要一行,可以使用聚合:
注意,如果每个id可以有多个成功日期,那么可以使用
array_agg()
: