表:贷款
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 号码识别)。这
结果应如下:
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:
SELECT *
FROM loan
GROUP BY SSS_no
ORDER 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)
FROM loan
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
有人可以帮我解决我的问题吗?谢谢。
试试这个:
SELECT l1.*
FROM loan AS l1
INNER JOIN
(
SELECT SSS_no, MAX(Loan_date) LatestDate
FROM loan
GROUP BY SSS_no
) AS l2 ON l1.SSS_no = l2.SSS_no
AND l1.loan_date = l2.LatestDate;
SQL 小提琴演示 http://www.sqlfiddle.com/#!2/709939/1
这会给你:
| LOAN_NO | AMOUNT | SSS_NO | LOAN_DATE |
----------------------------------------------
| 16 | 230 | 104849222 | 2013-03-06 |
| 17 | 265 | 119611199 | 2011-04-30 |
| 18 | 455 | 3317131410 | 2013-03-10 |
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)