我正在尝试创建一个sql(mariadb(请求,该请求选择多个列,但需要两列作为唯一的对,但要确保所选的对的created_at值小于其他duplita对。
以下是我的表格大致的样子:
id | 从_user_id | 到_user_id | 创建日期||
---|---|---|---|---|
1 | 1 | 2 | <100000005>||
2 | 2 | 1 | 100000002 | |
3 | 2 | 3 | 100000008 | |
4 | 5 | 6 | >999999999||
5 | 6 | 5 | 100000006 |
WITH
cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY GREATEST(from_user_id,to_user_id),
LEAST(from_user_id,to_user_id)
ORDER BY created_at) rn
FROM table
)
SELECT *
FROM cte
WHERE rn = 1