问题描述
表格:贷款
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
<小时>
预期结果:
我想检索最新的贷款由每个人(由他们的 SSS 号码识别).这结果应如下:
I would want to retrieve the latest loan availed off by each person (identified by their SSS number). The results 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
<小时>
使用的查询 # 1:
<代码>选择 *来自贷款GROUP BY SSS_noORDER BY Loan_date DESC
MYSQL 结果
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
<小时>
已使用的查询 # 2:
SELECT Loan_no, Amount, SSS_no, max(Loan_date)来自贷款GROUP BY SSS_no
MYSQL 结果
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.
推荐答案
MySQL 参考 提出了几种解决此问题的方法.最简单的是子查询:
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);
鉴于这种类型的子查询可能性能不佳，他们还建议使用 JOIN(基本上是 Mahmoud Gamal 的回答):
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;
第三个选项是:
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;
LEFT JOIN 的工作原理是，当l1.loan_date 为最大值时，后面还有l2.loan_date，所以l2 行值将为 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() 返回意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！