左加入最大/顶部

Question

我有两个表，我试图运行查询返回每个人的最大（或顶部）事务。我应该注意到，我不能改变表格结构。相反，我只能提取数据。

人民

 
+-----------+ 
| id | name | 
+-----------+ 
| 42 | Bob | 
| 65 | Ted | 
| 99 | Stu | 
+-----------+ 

交易（没有主键）

 
+---------------------------------+ 
| person  | amount | date  | 
+---------------------------------+ 
| 42   | 3  | 9/14/2030 | 
| 42   | 4  | 7/02/2015 | 
| 42   | *NULL* | 2/04/2020 | 
| 65   | 7  | 1/03/2010 | 
| 65   | 7  | 5/20/2020 | 
+---------------------------------+ 

最终，每个人​​我想回到的最高金额。如果这不起作用，那么我想查看日期并返回最近的日期。

所以，我想我的查询返回：

 
+----------------------------------------+ 
| person_id | name | amount | date  | 
+----------------------------------------+ 
| 42   | Bob | 4  | 7/02/2015 | (<- highest amount) 
| 65   | Ted | 7  | 5/20/2020 | (<- most recent date) 
| 99   | Stu | *NULL* | *NULL* | (<- no records in Transactions table) 
+----------------------------------------+ 

---

SELECT People.id, name, amount, date 
FROM People 
LEFT JOIN (
    SELECT TOP 1 person_id 
    FROM Transactions 
    WHERE person_id = People.id 
    ORDER BY amount DESC, date ASC 
) 
ON People.id = person_id 

我想不出什么我做错了，但我知道这是错的。任何帮助将非常感激。

Answer 1

你就要成功了，但因为有副本ID在交易表，所以你需要使用ROW_NUMBER（）函数 ，以消除那些试试这个：

With cte as 
(Select People,amount,date ,row_number() over (partition by People 
            order by amount desc, date desc) as row_num 
    from Transac) 
Select * from People as a 
left join cte as b 
on a.ID=b.People 
and b.row_num=1 

结果是Sql Fiddle

编辑：ROW_NUMBER（）从MSDN

Returns the sequential number of a row within a partition of a result set, 
starting at 1 for the first row in each partition. 

分区用于组结果集和超过由子句用于

Determine the partitioning and ordering of the rowset before the 
associated window function is applied.