在一对多关系中检索最高值

问题描述

我有一个贷款表和一个属性表。一对多的关系。如果贷款包含不止一项财产，我只需要检索具有最高评估价值的财产。

I have a loan table and a properties table. One to many relationship. If the loan contains more than one property, I have to only retrieve the property that has the highest appraisal value.

以下查询

SELECT l.loan_id,p_count
FROM loans lo
JOIN
  (SELECT loan_id, MAX(appraised_value) AS val
          COUNT(property_id) AS p_count
   FROM properties
   GROUP BY loan_id) AS pc ON pc.loan_id = lo.id

提供输出

loan_id val      p_count
817     914,000  2

但是，如果我尝试从属性表中检索其他属性（例如类型，地址），则会获得所有记录该贷款的属性（在本例中为2）。我写了这个查询

But if I attempt to retrieve additional attributes (e.g type, address) from the properties table, I get records all the properties for that loan (2, in this case). I wrote this query

SELECT l.loan_id,p_count
FROM loans lo
JOIN
  (SELECT loan_id, MAX(appraised_value), type, address AS val
          COUNT(property_id) AS p_count
   FROM properties
   GROUP BY loan_id) AS pc ON pc.loan_id = lo.id

获得以下输出：

loan_id val      p_count  type     address
817     800,000  2        duplex   123 main street
817     914,000  2        triplex  234 cedar avenue

如何获得最高评估值的以下输出？

How can I get the following output for the highest appraised value?

loan_id  val      p_count  type      address
817      914,000  2        triplex   234 cedar avenue

推荐答案

您可以使用窗口函数来计算每笔贷款的属性计数和同一查询中的最高价值：

You can use window functions to calculate the count of properties per loan and the highest value in the same query:

SELECT lo.id, pc.p_count, pc.appraised_value, pc.type, pc.address
FROM loans lo
JOIN (
  SELECT loan_id, 
         appraised_value,
         type,
         address,
         count(*) over (partition by loan_id) AS p_count,
         dense_rank() over (partition by loan_id order by appraised_value desc) as rnk
   FROM properties
) AS pc ON pc.loan_id = lo.id and rnk = 1;

正如Josh所评论的，如果两个属性具有相同的最高appraised_value，则将同时列出这两个属性。如果您不想这样做，请使用 row_number（）而不是 dense_rank（）。

As Josh commented, if two properties have the same highest appraised_value, both will be listed. If you don't want that, use row_number() instead of dense_rank().

这篇关于在一对多关系中检索最高值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！