根据SQL中另一列的最大值选择两列 [英] select two columns based on the maximum value of the other column in SQL

问题描述

这是我说Loandetails的桌子

Hi this is the table Im using say Loandetails

Loanno  Balance amount  DueDATE
1001045 308731.770000   12/31/99
1001045 2007700.740000  12/31/99
1001045 3087318905.770000   11/01/99
1001045 308731.770000   12/31/99

我必须根据maximum value of Balance Amount选择Loanno和DueDate. Loanno不是唯一的.请帮我解决这个问题.

I have to select Loanno and DueDate based on the maximum value of Balance Amount. Loanno is not unique.Please help me out on this.

推荐答案

尝试一下:

SELECT L.Loanno, L.Balanceamount, L.DueDate
     FROM dbo.Loan L 
     INNER JOIN 
         (
          SELECT Loanno, MAX(Balancemount) as MaxBalance FROM dbo.Loan
             GROUP BY LoanNo
         ) SUB ON L.Loanno = SUB.Loanno AND L.Balanceamount = SUB.MaxBalance

子查询为每个LoanNo(无论日期如何)都返回Maximum balance 回到原始表格后，您将剩下贷款编号，最大余额和到期日.

The sub query returns the Maximum balance for each LoanNo (regardless of date) When joined back to your original table you are left with the LoanNo, Maximum Balance and Date at which this is Due.

好吧，刚刚在MS Acccess中测试了以下查询，它工作正常，将Table1替换为您的实际表名:

Ok just tested the query below in MS Acccess and it works just fine, substitute Table1 with your actual table name:

SELECT T.LoanNo, T.DueDate, T.BalanceAmount
 FROM Table1 As T
  INNER JOIN (
     SELECT T.Loanno, Max([T.Balanceamount]) AS MaxBalance
     FROM Table1 as T
     GROUP BY T.Loanno) SUB ON T.LoanNo = SUB.LoanNo AND T.BalanceAmount = SUB.MaxBalance

这篇关于根据SQL中另一列的最大值选择两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！