SQL2000求每个会员最后消费信息
一共有两张表，一张会员信息表(hyxx);一张会员消费表(xfxx);两张表都有会员卡号(hykh)字段；消费信息表有字段ID保存的自动生成的消费单号；
现要求会员消费表(xfxx)根据会员信息表(hyxx)中的会员查找出会员消费表中“每个”会员的最后一次消费记录信息消费时间字段为(xfrq)；不能有重复记录出现，假如有1000个会员那查出来就是1000条数据；
我自己写了SQL语句：（
SELECT TOP 100 PERCENT dbo.hyxx.hykh, dbo.hyxx.hyxm, dbo.hyxx.klxmc,
dbo.hyxx.kmc, dbo.hyxx.jrrq, dbo.hyxx.yddh, dbo.hyxx.hyjf, x.xfrq, x.fdid, x.hyfdid,
x.xfje, dbo.hyxx.hyje
FROM dbo.hyxx LEFT JOIN
dbo.xfxx x ON x.hykh = dbo.hyxx.hykh
WHERE (x.id =
(SELECT TOP 1 id
FROM xfxx
WHERE hykh = hyxx.hykh
ORDER BY xfrq DESC))
ORDER BY dbo.hyxx.hykh DESC
）
测试数据会员为2万多，消费记录为60万左右；数据库是SQL2000的，查询效率为11秒才能查询到；效率相当不高，
求广大网游前辈帮忙看有什么更好的办法解决我的问题

[解决办法]

SQL code

SELECT TOP 100 PERCENT        dbo.hyxx.hykh, dbo.hyxx.hyxm, dbo.hyxx.klxmc, dbo.hyxx.kmc, dbo.hyxx.jrrq, dbo.hyxx.yddh, dbo.hyxx.hyjf, x.xfrq, x.fdid, x.hyfdid, x.xfje, dbo.hyxx.hyjeFROM    dbo.hyxxLEFT JOIN dbo.xfxx xON      x.hykh = dbo.hyxx.hykhWHERE   NOT EXISTS ( SELECT 1                     FROM   xfxx                     WHERE  hykh = hyxx.hykh                            AND xfrq > hyxx.xfrq )ORDER BY dbo.hyxx.hykh DESC
[解决办法]
SQL code
SELECT TOP 100 PERCENT dbo.hyxx.hykh, dbo.hyxx.hyxm, dbo.hyxx.klxmc,   dbo.hyxx.kmc, dbo.hyxx.jrrq, dbo.hyxx.yddh, dbo.hyxx.hyjf, x.xfrq, x.fdid, x.hyfdid,   x.xfje, dbo.hyxx.hyjeFROM dbo.hyxx LEFT JOIN  dbo.xfxx x ON x.hykh = dbo.hyxx.hykh  outer apply (SELECT TOP 1 id               FROM xfxx               WHERE hykh = hyxx.hykh               ORDER BY xfrq DESC)) dWHERE x.id = d.idORDER BY dbo.hyxx.hykh DESC--try!
[解决办法]
探讨

SQL code

SELECT TOP 100 PERCENT dbo.hyxx.hykh, dbo.hyxx.hyxm, dbo.hyxx.klxmc, 
  dbo.hyxx.kmc, dbo.hyxx.jrrq, dbo.hyxx.yddh, dbo.hyxx.hyjf, x.xfrq, x.fdid, x.hyfdid, 
  x.xfje, dbo.hyxx.hyje
FRO……

[解决办法]
select a.hykh,c.hyxm,c.klxmc,c.kmc,c.jrrq,c.yddh,c.hyjf,B.xfrq,
 B.fdid,B.hyfdid,B.xfje,B.hyje
 from (select hykh,MAX(xfrq) xfrq from xfxx group by hykh) a
  left join xfxx b on a.hykh = B.hykh and a.xfrq = B.xfrq
  left join hyxx c on a.hykh = c.hykh

试试这样会不会快点