select max(photoid),max(client_id),max(scan_date),... from 表名 group by client_id
select min((sysdate-scan_date)*1000000000+photoid)-min((sysdate-scan_date)*1000000000), client_id, max(scan_date) from table2 group by client_id;
select a.client_id,a.client_name,b.photoid,b.client_id,b.scan_date from table1 a left join ( select k.photoid,k.client_id,k.scan_date from table2 k,(select client_id,max(photoid) photoid from table2 group by client_id) s where k.client_id=s.client_id and k.photoid=s.photoid ) b on a.client_id=b.client_id
select client_id,max(scan_date) as scan_da from table2 group by client_id 这个语句就足够了,简单有效, 至于效率低,不是sql的问题,关键是看索引了。 楼主把scan_date也创建索引再试一试。
--如果你的记录是按时间顺序插入表中的,可以用下面的语句。select * from table2 a,(select max(rowid) no from table2 group by client_id) b where a.rowid=b.no;
photoid, client_id, scan_date ....(photo_id是主键) 记录数有400多万另外补充一下:有的client_id在表Table2中没有记录
client_id,
max(scan_date)
from table2
group by client_id;
select k.photoid,k.client_id,k.scan_date from table2 k,(select client_id,max(photoid) photoid from table2 group by client_id) s where
k.client_id=s.client_id and k.photoid=s.photoid ) b on a.client_id=b.client_id
这个语句就足够了,简单有效,
至于效率低,不是sql的问题,关键是看索引了。
楼主把scan_date也创建索引再试一试。
--如果你的记录是按时间顺序插入表中的,可以用下面的语句。
select * from table2 a,(select max(rowid) no from table2 group by client_id) b where a.rowid=b.no;
rowid并不是严格按照记录顺序来排序的吧
同意zhengg21(很努力) 的意见
要与Table1关键啊