我目前在一个项目中,我使用for循环创建一个元组列表的形式(id_one, id_two),其中从下面两个查询的ranking_id's是相等的。
如果有几行相同的ranking_id,那么查询从数据库中选择最新更新的行是很重要的。否则顺序无关。
我想知道是否有一个快速的单一查询来取代for循环和两个单一查询。非常感谢你的帮助。干杯!
#查询一
cur.execute(f''' SELECT DISTINCT ON (ranking_id) ranking_id, id
FROM {my_table}
WHERE user_id = ({user_id_one})
ORDER BY ranking_id ASC, updated_at DESC''')
list_one = cur.fetchall()
两个
#查询
cur.execute(f''' SELECT DISTINCT ON (ranking_id) ranking_id, id
FROM {my_table}
WHERE user_id = ({user_id_two})
ORDER BY ranking_id ASC, updated_at DESC''')
list_two = cur.fetchall()
我使用python和postgresql。(我不是问如何写for循环)
Test
WITH cte AS ( SELECT user_id,
ranking_id,
id,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ranking_id ASC, updated_at DESC) rn
FROM {my_table} )
SELECT user_id, ranking_id, id
FROM cte
WHERE rn = 1;
您可以通过修改DISTINCT ON
和ORDER BY
子句将查询扩展到两个用户:
SELECT DISTINCT ON (user_id, ranking_id) ranking_id, id
FROM {my_table}
WHERE user_id IN ({user_id_one}, {user_id_one})
ORDER BY user_id, ranking_id ASC, updated_at DESC;