如何选择最近的日期项

Question

我哈瓦表USER：

select t.* from user t; 
uid uname 
1 tom 
2 jim 
3 bob 
4 lily 

还有桌上玩具

select t.* from toys t; 
tid uid tdate 
1 1 7/12/15 
2 1 6/12/15 
3 2 9/12/15 
4 2 10/12/15 
5 3 12/12/15 

现在我想

uid tid uname tdate 
1 2  tom 6/12/15 
2 3  jim 9/12/15 
3 5  bob 12/12/15 
4   lily 

我应该怎么办？ （我使用oracle数据库）;

SELECT a.uid, b.tid, a.uname, MAX(b.tdate) AS tdate 
FROM user a LEFT JOIN toys b ON a.uid = b.uid 
GROUP BY a.uid, b.tid, a.uname 

或最近的玩具每用户（如果有的话）：

SELECT a.uid, b.tid, a.uname, MAX(b.tdate) 
FROM user a LEFT JOIN toys b ON a.uid = b.uid 
GROUP BY a.uid, a.uname 

Answer 1

你可以为每个用户序列，仅拉出后

Answer 2

最近每个玩具和用户tdate每个玩具的最短日期：

SELECT user.uid, toys.tid, user.name, toys.tdate 
    FROM  
     (SELECT tid, uid, tdate, 
     ROW_NUMBER() OVER (PARTITION BY uid ORDER BY tdate asc) 
     AS SEQ 
     FROM toys 
    )table1 
LEFT JOIN user on toys.uid = user.uid 
WHERE SEQ = 1