sql根据状态获取第一个非值

bfrts1fy  于 2021-07-24  发布在  Java
关注(0)|答案(4)|浏览(272)

我不确定标题是否正确,但这是我的问题。我有一张这样的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 |

希望有人能对如何处理这种情况有所帮助。

lh80um4z

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';

输出:
idcityprovincestatusdate
1caintarizalsuccess02/01/2020
2pasigmetro manilasuccess06/01/2020
3obandobulacansuccess09/01/2020
gorkyyrv

gorkyyrv2#

也许窗口函数可以帮助:

SELECT id, city, province, status, date
FROM (SELECT id,
             max(city) OVER w AS city,
             max(province) OVER w AS province,
             status,
             date
      FROM atable
      WINDOW w AS (PARTITION BY id)) AS q
WHERE status = 'success';
z2acfund

z2acfund3#

你可以在这里使用分析函数。

SELECT * FROM 
(SELECT T.ID, T.CITY, T.PROVINCE,
        MAX(CASE WHEN STATUS = 'success' THEN DATE END) 
             OVER (PARTITION BY ID ORDER BY DATE) AS DATE,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE) AS RN,
        SUM(CASE WHEN STATUS = 'success' THEN 1 ELSE 0 END) 
             OVER (PARTITION BY ID) AS CNT
  FROM YOUR_TABLE T)
 WHERE RN = 1 AND CNT > 0

由于更改了示例数据,因此可以使用 GROUP BY 具体如下:

SELECT ID, MAX(CITY) AS CITY, MAX(PROVINCE) AS PROVINCE, 
       MAX(CASE WHEN STATUS = 'success' THEN DATE END) AS DATE
  FROM YOUR_TABLE 
GROUP BY ID
HAVING SUM(CASE WHEN STATUS = 'success' THEN 1 END) > 0
dwthyt8l

dwthyt8l4#

如果每个id只需要一行,可以使用聚合:

select id, max(city) as city, max(province) as province,
       max(date) filter (where status = 'success') as date
from t
group by id
having count(*) filter (where status = 'success') > 0;

注意,如果每个id可以有多个成功日期,那么可以使用 array_agg() :

array_agg(date) filter (where status = 'success') as dates

相关问题