mysql - 我如何编写这种查询(为每一行返回最新的可用数据)

Question

我有一个这样定义的表:

CREATE TABLE mytable (id            INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), 
                      user_id       INT REFERENCES user(id) ON UPDATE CASCASE ON DELETE RESTRICT,
                      amount        REAL NOT NULL CHECK (amount > 0),
                      record_date   DATE NOT NULL
                     );
CREATE UNIQUE INDEX idxu_mybl_key ON mytable (user_id, amount, record_date);

我想编写一个包含两列的查询:

• 用户编号
• 金额

在给定用户的返回结果集中应该只有一个条目。此外，返回的金额数字应该是用户最后记录的金额(即 MAX(record_date)。

之所以会出现这种情况，是因为权重是在不同日期为不同用户记录的，因此没有针对所有用户的单一 LAST record_date。

我如何编写(最好是 ANSI SQL)查询以返回前面提到的列，但确保返回的只是用户最后记录的金额？

顺便说一句，在查询中也返回“record_date”列可能是个好主意，这样可以更轻松地验证查询是否按要求工作。

我使用 MySQL 作为我的后端数据库，但理想情况下，查询应该尽可能与数据库无关(即 ANSI SQL)。

Answer 1

首先，您需要每个用户的最后一个 record_date:

select user_id, max(record_date) as last_record_date
from mytable
group by user_id

现在，您可以将之前的查询与 mytable 本身结合起来以获得此 record_date 的 amount:

select 
      t1.user_id, last_record_date, amount
from 
      mytable  t1
  inner join
      ( select user_id, max(record_date) as last_record_date
        from mytable
        group by user_id 
      ) t2
    on  t1.user_id = t2.user_id 
    and t1.record_date = t2.last_record_date

出现问题是因为用户可以为相同的 last_record_date(具有不同的金额)设置多行。然后你应该得到其中之一，样本(获得不同数量的最大值):

select 
      t1.user_id, t1.record_date as last_record_date, max(t1.amount)
from 
      mytable  t1
  inner join
      ( select user_id, max(record_date) as last_record_date
        from mytable
        group by user_id 
      ) t2
    on  t1.user_id = t2.user_id 
    and t1.record_date = t2.last_record_date
group by t1.user_id, t1.record_date