sql - 左加入最大值/顶部

Question

我有两个表，我试图从中运行查询以返回每个人的最大(或最高)交易。我应该注意，我无法更改表结构。相反，我只能拉数据。

人

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

Transactions (there is no primary key)

+---------------------------------+| 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 |  +---------------------------------+

Ultimately, for each person I want to return the highest amount. If that doesn't work then I'd like to look at the date and return the most recent date.

So, I'd like my query to return:

+----------------------------------------+| 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

您快到了，但是由于 Transaction 表中有重复的 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

编辑:来自 MSDN 的 Row_number()

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

分区用于对结果集进行分组，并使用 Over by 子句

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