求购 SQL 语句 在线等待

[ghtghtmalone]

TABLE A
CARD_NO VARCHAR(6),
CONSUME_AMOUNT MONEY,
REST_AMOUNT MONEY,
CONSUME_TIME DATETIME
数据如下：
000001 30 100 2002-10-12 10：10：00
000001 30 100 2002-10-12 10：30：00
000002 30 100 2002-10-12 10：50：00
000002 30 100 2002-10-12 10：30：00
000003 30 100 2002-10-12 10：10：00
000003 30 100 2002-10-12 10：30：00
000003 30 100 2002-10-12 20：50：00
想得到如下结果：（列出某一卡号最近一次消费的，卡号、消费金额、剩余金额、消费时间）
000001 30 100 2002-10-12 10：30：00
000002 30 100 2002-10-12 10：50：00
000003 30 100 2002-10-12 20：50：00
谢谢！

[p2bl]

select * from
(
select CARD_NO,MAX(CONSUME_TIME) from A group by CARD_NO) aa inner join A
on A.CARD_NO=aa.CARD_NO and A.CONSUME_TIME=aa.CONSUME_TIME

[p2bl]

上面的select *应该为select A.*

[票票飞扬]

SELECT CARD_NO,CONSUME_AMOUNT MONEY,REST_AMOUNT,CONSUME_TIME
FROM TableA A,(SELECT CARD_NO,MAX(CONSUME_TIME) AS MAXTIEM FROM TableA GROUP BY CARD_NO) B
WHERE A.CARD_NO = B.CARD_NO
AND A.CONSUME_TIME = B.MAXTIME


VARCHAR(6),

MONEY,
DATETIME
))

[playyuer]

select *
from A T
where CONSUME_TIME = (select max(CONSUME_TIME)
from A
where CARD_NO = T.CARD_NO)

[ghtghtmalone]

谢谢 楼上的兄弟
可编译时出错！
SERVER：MSG 8155，LEVEL16， STATE 2，LINE 1
NO COLUMN WAS SPECIFIED FOR COLUMN2 OF ‘AA’。

[CoolAbu]

select * from A t1 ,
(select CARD_NO,max(CONSUME_TIME) as maxtime from A group by CARD_NO) b
where t1.CARD_NO=b.CARD_NO and t1.CONSUME_TIME=b.maxtime order by t1.CARD_NO

[CoolAbu]

select *
from A T
where CONSUME_TIME = (select max(CONSUME_TIME)
from A
where CARD_NO = T.CARD_NO) order by T.CARD_NO

[ghtghtmalone]

谢谢楼上的各位老大的帮助！
现已用票票兄弟的代码调通！
谢谢！

[playyuer]

有眼无珠:(

[playyuer]

建议参阅:
http://expert.csdn.net/Expert/TopicView1.asp?id=1252249

[ghtghtmalone]

谢谢小干部儿，
你的方法很好！