MySQL groupwise MAX（）返回意外的结果

本文介绍了MySQL groupwise MAX（）返回意外的结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

表：贷款

  Loan_no金额SSS_no贷款期限
 
 7 700.00 0104849222 2010-01-03 
 8 200.00 0104849222 2010-02-28 
 9 300.00 0119611199 2010-11-18 
 10 150.00 3317131410 2012-11-28 
 11 600.00 0104849222 2011-01-03 
 14 175.00 3317131410 2012-12-05 
 15 260.00 3317131410 2013-02-08 
 16 230.00 0104849222 2013-03-06 
 17 265.00 0119611199 2011- 04-30 
 18 455.00 3317131410 2013-03-10

期望的结果：

我想检索每个人获得的
的最新贷款他们的SSS号码）。
的结果应如下所示：

Loan_no金额SSS_no借款日期 16 230.00 0104849222 2013-03-06 17 265.00 0119611199 2011-04-30 18 455.00 3317131410 2013-03-10

QUERY＃1 USED： b
$ b
SELECT * FROM loan GROUP BY SSS_no ORDER BY Loan_date DESC

MYSQL RESULT
Loan_no金额SSS_no贷款期限 10 150.00 3317131410 2012-11-28 9 300.00 0119611199 2010-11-18 7 700.00 0104849222 2010-01-03

QUERY＃2 USED：

SELECT Loan_no，Amount，SSS_no，max（Loan_date） FROM loan GROUP BY SSS_no

MYSQL RESULT
Loan_no金额SSS_no贷款_date 7 700.00 0104849222 2013-03-06 9 300.00 0119611199 2011-04-30 10 150.00 3317131410 2013-03-10
任何人都可以帮我解决问题吗？感谢。
解决方案
提出了几种解决方法。最简单的是一个子查询：

$ pre code $ SELECT
FROM loan l1
WHERE loan_date =（SELECT MAX（ l2.loan_date）
FROM loan l2
WHERE l1.sss_no = l2.sss_no）;

鉴于，他们还建议使用 JOIN Mahmoud Gamal的回答）：

pre code> SELECT l1.loan_no，l1.amount，l1.sss_no，l1.loan_date
FROM贷款l1
JOIN（
SELECT loan_no，MAX（loan_date）AS loan_date
FROM loan
GROUP BY sss_no）AS l2
ON l1.loan_date = l2.loan_date AND l1.sss_no = l2.sss_no;

第三种选择是：

SELECT l1.loan_no，l1.amount，l1.sss_no，l1.loan_date FROM loan l1 LEFT JOIN贷款l2 ON l1.sss_no = l2.sss_no AND l1。 loan_date< l2.loan_date WHERE l2.sss_no IS NULL;
LEFT JOIN 的工作原理是当 l1.loan_date 处于最大值时，后面会出现 l2.loan_date ，所以l2行值将会是NULL。

所有这些应该有相同的输出，但可能会有不同的表现。

TABLE: LOAN
Loan_no Amount SSS_no Loan_date 7 700.00 0104849222 2010-01-03 8 200.00 0104849222 2010-02-28 9 300.00 0119611199 2010-11-18 10 150.00 3317131410 2012-11-28 11 600.00 0104849222 2011-01-03 14 175.00 3317131410 2012-12-05 15 260.00 3317131410 2013-02-08 16 230.00 0104849222 2013-03-06 17 265.00 0119611199 2011-04-30 18 455.00 3317131410 2013-03-10
DESIRED RESULTS:
I would want to retrieve the latest loan availed offby each person (identified by their SSS number). Theresults should be as follows:
Loan_no Amount SSS_no Loan_date 16 230.00 0104849222 2013-03-06 17 265.00 0119611199 2011-04-30 18 455.00 3317131410 2013-03-10
QUERY # 1 USED:
SELECT *FROM loanGROUP BY SSS_noORDER BY Loan_date DESC
MYSQL RESULT
Loan_no Amount SSS_no Loan_date 10 150.00 3317131410 2012-11-28 9 300.00 0119611199 2010-11-18 7 700.00 0104849222 2010-01-03
QUERY # 2 USED:
SELECT Loan_no, Amount, SSS_no, max(Loan_date)FROM loanGROUP BY SSS_no
MYSQL RESULT
Loan_no Amount SSS_no Loan_date 7 700.00 0104849222 2013-03-06 9 300.00 0119611199 2011-04-30 10 150.00 3317131410 2013-03-10
Can anybody help me with my problem? Thanks.
解决方案
The MySQL reference suggests several ways to solve this. The simplest is a subquery:
SELECT * FROM loan l1 WHERE loan_date=(SELECT MAX(l2.loan_date) FROM loan l2 WHERE l1.sss_no = l2.sss_no);
Given that this type of subqueries potentially have bad performance, they also suggest using a JOIN (essentially Mahmoud Gamal's answer):
SELECT l1.loan_no, l1.amount, l1.sss_no, l1.loan_date FROM loan l1 JOIN ( SELECT loan_no, MAX(loan_date) AS loan_date FROM loan GROUP BY sss_no) AS l2 ON l1.loan_date = l2.loan_date AND l1.sss_no = l2.sss_no;
A third option is:
SELECT l1.loan_no, l1.amount, l1.sss_no, l1.loan_date FROM loan l1 LEFT JOIN loan l2 ON l1.sss_no = l2.sss_no AND l1.loan_date < l2.loan_date WHERE l2.sss_no IS NULL;
The LEFT JOIN works on the basis that when l1.loan_date is at its maximum value, there is later l2.loan_date, so the l2 row values will be NULL.
All these should have the same output, but likely differ in performance.

这篇关于MySQL groupwise MAX（）返回意外的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！