我用sql2000，我用一个表A，表里有卡号（icid）计数器号（counter），消费时间等，我现在想查询列出每张卡

显示全部楼层 · 2010-12-21 12:04:15

我用sql2000，我用一个表A，表里有卡号（icid）计数器号（counter），消费时间等，我现在想查询列出每张卡的最大计数器号的那条消费记录

千问 · 2010-12-21 12:04:15

这个肯定可以SELECT *FROM A tWHERE NOT EXISTS(SELECT 1 FROM A WHERE icid = t.icid AND counter > t.counter)

千问 · 2010-12-21 12:04:15

select * from A join (select icid,max(counter) as acounter from A group by icid) as atabon A.icid=atab.icid and A.counter=atab.acounter差点忘了怎么写了...

千问 · 2010-12-21 12:04:15

select * from a inner join (select icid,max(counter)as maxcountfrom A group by icid) b on a.icid =b.icid and a.counter = b.maxcount

千问 · 2010-12-21 12:04:15

先取得每个卡的最大计数器号，再和原表做关联：select * from Aleft join (select max(counter),icid from A group by icid) Bon A.icid=B.icid and A.counter=B.counter