根据行值获取sql数据

Question

我有一个这样的表:

date       |    id
------------------------
2022-04-01 |    1
2022-04-02 |    1
2022-04-03 |    1
2022-04-01 |    2
2022-04-03 |    2
2022-04-02 |    3

我试图得到最后的日期，当帐户是活跃的，不包括今天。如果id在今天的日期不存在，则不包含在最终表中。对于id 1，它将是2022-04-02，对于id 2，它将是2022-04-01

我可以执行select max(ds) from table where date != "2022-04-03"以获取最后日期，但是我如何以以下格式获取每个帐户的最后日期:

date       |   id
------------------
2022-04-02 |   1
2022-04-01 |   2

即使我尝试子查询它，我不确定子查询应该是什么。我试过:

with ids as (select id from table where date="2022-04-03")
select max(date), id from table where id in ids.id

，但它给我以下错误:cannot recognize input near 'ids' '.' 'id' in expression specification

Answer 1

您可以使用窗口函数ROW_NUMBER来获得您想要的日期

WITH CTE AS (select id,date, ROW_NUMBER() OVER(PARTITION BY id ORDER BY Date DESC) rn from table)
SELECT id,date FROM CTE where rn = 2

Torpas是对的，排除今天的登录，并在with子句中添加另一个条件

WITH CTE AS (select id,date, ROW_NUMBER() OVER(PARTITION BY id ORDER BY Date DESC) rn from table WHERE id NOT IN ( SELECT id from table WHERE date = current_date()))
SELECT id,date FROM CTE where rn = 2

Answer 2

如果我理解正确，您可以尝试使用IN子查询

select max(date), id 
from table 
where id IN (
select id 
from table 
where date='2022-04-03'
)
GROUP BY id

Answer 3

你可以使用子查询来得到你想要的结果-

SELECT MAX(date_), id    -- I have used date column as date_ as date is reserved word.
FROM (SELECT date_, id
FROM your_table
WHERE date_ < DATE '2022-04-03'); -- Since you need the last day excluding today's date.
GROUP BY id;