获取最后一组不同的记录 [英] Get last distinct set of records

查看:48
本文介绍了获取最后一组不同的记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个包含以下列的数据库表:

I have a database table containing the following columns:

id   code   value   datetime   timestamp

在此表中,唯一的唯一值位于ID(即主键)中.

In this table the only unique values reside in id i.e. primary key.

我想基于datetime值检索该表中的最后一组不同的记录.例如,下面说的是我的桌子

I want to retrieve the last distinct set of records in this table based on the datetime value. For example, let's say below is my table

id   code   value   datetime               timestamp
1    1023   23.56   2011-04-05 14:54:52    1234223421
2    1024   23.56   2011-04-05 14:55:52    1234223423
3    1025   23.56   2011-04-05 14:56:52    1234223424
4    1023   23.56   2011-04-05 14:57:52    1234223425
5    1025   23.56   2011-04-05 14:58:52    1234223426
6    1025   23.56   2011-04-05 14:59:52    1234223427
7    1024   23.56   2011-04-05 15:00:12    1234223428
8    1026   23.56   2011-04-05 15:01:14    1234223429
9    1025   23.56   2011-04-05 15:02:22    1234223430

我想检索ID为4、7、8和9的记录,即具有不同代码(基于日期时间值)的最后一组记录.我强调的只是我要实现的目标的一个示例,因为该表最终将包含数百万条记录和数百个单独的代码值.

I want to retrieve the records with IDs 4, 7, 8, and 9 i.e. the last set of records with distinct codes (based on datetime value). What I have highlighted is simply an example of what I'm trying to achieve, as this table is going to eventually contain millions of records, and hundreds of individual code values.

我可以使用哪种SQL语句来实现这一目标?我似乎无法通过单个SQL语句完成它.我的数据库是MySQL 5.

What SQL statement can I use to achieve this? I can't seem to get it done with a single SQL statement. My database is MySQL 5.

推荐答案

这应该对您有用.

 SELECT * 
 FROM [tableName] 
 WHERE id IN (SELECT MAX(id) FROM [tableName] GROUP BY code)

如果id为AUTO_INCREMENT,则无需担心datetime的计算成本,因为最新的datetime也会具有最高的id.

If id is AUTO_INCREMENT, there's no need to worry about the datetime which is far more expensive to compute, as the most recent datetime will also have the highest id.

更新:从性能的角度来看,在处理大量记录时,请确保对idcode列建立索引.如果id是主键,则它是内置键,但是您可能需要添加覆盖codeid的非聚集索引.

Update: From a performance standpoint, make sure the id and code columns are indexed when dealing with a large number of records. If id is the primary key, this is built in, but you may need to add a non-clustered index covering code and id.

这篇关于获取最后一组不同的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆