查找2个用户POSTGRES的最小时间戳

Question

这是我的table_gamers:

<表类> game_id user1 user2 时间戳 tbody><<tr>18901232022-01-0121237682022-02-09

做一步一步的一些with_clauses:首先获取所有匹配user1-user2, user2-user1其次，通过时间戳排序给出一些id第三，得到你想要的:

with base_data as (
select game_id,user1,user2,timestamp from table_gamers
union all 
select game_id,user2,user1,timestamp from table_gamers
),
base_id as (
select
row_number() over (order by base_data.timestamp) as id,
row_number() over (PARTITION by base_data.user1 order by base_data.timestamp) as id_2,
*
from base_data
)

select * from base_id
where id_2 = 1 order by timestamp

在retults

id  id_2    game_id user1   user2   timestamp
2   1   1   123 890 2022-01-01T00:00:00.000Z
1   1   1   890 123 2022-01-01T00:00:00.000Z
4   1   2   768 123 2022-02-09T00:00:00.000Z

我希望这能给你正确的想法

https://www.db-fiddle.com/f/9PrxioFeVaTmtVcYdteovj/0