MySQL groupwise MAX() 返回意外结果

2021-12-26 00:00:00 sql group-by mysql greatest-n-per-group

表格:贷款

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.

相关文章