有个数据表,如下 drop table if exists test1;
create table test1 (id INT NOT NULL,
archivetime datetime not null,
filename text not null)
ENGINE=MyISAM;
insert into test1 values
(2,'2013-09-24 ','File-01.txt'),
(3,'2013-09-25 ','File-01.txt'),
(4,'2013-09-25 ','File-03.txt'),
(5,'2013-09-25 ','File-04.txt'),
(6,'2013-09-24 ','File-04.txt');
想要取出文件名时间最大文件名和ID,期望结果是
+----+---------------------+-------------+
| id | archivetime | filename |
+----+---------------------+-------------+
| 3 | 2013-09-25 00:00:00 | File-01.txt |
| 4 | 2013-09-25 00:00:00 | File-03.txt |
| 5 | 2013-09-25 00:00:00 | File-04.txt |
+----+---------------------+-------------+需要说明的是,test1这个表文件数目很多,希望查询有效率,自己写的一个,查询效率低。 SELECT *
from test1
where archivetime in
(select MAX(archivetime)
from test1 group by filename)
GROUP BY filename ;
create table test1 (id INT NOT NULL,
archivetime datetime not null,
filename text not null)
ENGINE=MyISAM;
insert into test1 values
(2,'2013-09-24 ','File-01.txt'),
(3,'2013-09-25 ','File-01.txt'),
(4,'2013-09-25 ','File-03.txt'),
(5,'2013-09-25 ','File-04.txt'),
(6,'2013-09-24 ','File-04.txt');
想要取出文件名时间最大文件名和ID,期望结果是
+----+---------------------+-------------+
| id | archivetime | filename |
+----+---------------------+-------------+
| 3 | 2013-09-25 00:00:00 | File-01.txt |
| 4 | 2013-09-25 00:00:00 | File-03.txt |
| 5 | 2013-09-25 00:00:00 | File-04.txt |
+----+---------------------+-------------+需要说明的是,test1这个表文件数目很多,希望查询有效率,自己写的一个,查询效率低。 SELECT *
from test1
where archivetime in
(select MAX(archivetime)
from test1 group by filename)
GROUP BY filename ;
where NOT EXISTS
(SELECT 1 from test1 WHERE A.filename=filename AND A.archivetime<archivetime)在filename、archivetime上建立索引
仅供参考
[征集]分组取最大N条记录方法征集,及散分....
楼主可以简单查询一下 exists用法
数据量比较大就创建索引
sql语句
select * from test1 a where not exists
(select1 from test1 where filename=a.filename and archivetime>a.archivetime)