英文:
How to get a single record in a one-to-many relationship
问题
在一对多关系中，用户拥有多个合同。然而，我只想要特定一组用户的最新合同。
我的查询获取所有用户的合同如下所示：
SELECT userid FROM contract
WHERE userid IN (123, 143, 153, 163);
我天真地认为以下查询可以返回WHERE子句中的4个用户的最新合同。然而，它仅限于整个结果集的1条记录。
SELECT userid FROM contract
WHERE userid IN (123, 143, 153, 163)
ORDER BY signingdate DESC LIMIT 1 OFFSET 0;
如何修复我的查询以获取具有一对多关系的最新记录？
英文:
In a one-to-many relationship, users have many contracts. However, I only want the most recent contract for a specific set of users.
My query to get all contracts for users look like this:
SELECT userid FROM contract
WHERE userid IN (123, 143, 153, 163);
I naively thought the following query could return the most recent contract for the 4 users in the WHERE clause. However, it only limits to 1 record for the entire result set.
SELECT userid FROM contract
WHERE userid IN (123, 143, 153, 163)
ORDER BY signingdate DESC LIMIT 1 OFFSET 0
How can I fix my query to get the latest records that have a one-to-many relationship?
答案1
得分: 1
如果您知道如何创建子查询或使用常用表达式查询（CTE），那么以下内容适用于您。这将对每个用户ID中的结果进行编号，按签约日期排序。
SELECT userId FROM (
SELECT userId,
ROW_NUMBER() OVER (PARTITION BY userId ORDER BY signingdate DESC) AS rnDmy
FROM contract
WHERE userId IN (123, 143, 153, 163)
) WHERE rnDmy = 1
英文:
If you know how to make a subselect or with common table expression query (CTE), then the following will work for you. This numbers the results within each user ID, ordering by the signing date.
select userId from (
select userId,
row_number() over (partition by userId order by signingdate desc) as rnDmy
from contract
where userId in (123, 143, 153, 163)
) where rnDmy = 1
答案2
得分: 0
这应该有效
SELECT DISTINCT userid,
MAX(signingdate) OVER (PARTITION BY userid)
FROM contract
where userid in (123, 143, 153, 163)
更新：添加DISTINCT以消除重复的可能性。
英文:
This should Work
SELECT DISTINCT userid,
MAX(signingdate) OVER (PARTITION BY userid)
FROM contract
where userid in (123, 143, 153, 163)
UPDATE: added DISTINCT to eliminate possibility of duplicates
答案3
得分: 0
使用select distinct on而不是limit和offset：
select distinct on(userid)
userid
, signingdate
from contract
where userid in (123, 143, 153, 163)
order by userid
, signingdate desc;
英文:
Rather than limit and offset use select distinct on:
select distinct on(userid)
userid
, signingdate
from contract
where userid in (123, 143, 153, 163)
order by userid
, signingdate desc;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论