征求sql语句，最新一次购买时间

[jncharles]

一个用table中有三个字段
yhbh:用户编号
gmsj:购买时间
ye:余额
如何查找最近一次购买时间的那行记录，并插入另一个表中
如：
yhbh gmsj ye
001 2010-09-09 0.90
001 2010-09-10 0.80
001 2010-09-11 0.70
001 2010-09-12 0.60
002 2010-09-09 0.90
002 2010-09-10 0.80
我只想查询
001 2010-09-12 0.60
002 2010-09-10 0.80
这才是我需要的，如何查找

[水族杰纶]

select * 
from [table] t
where not exists(select 1
                 from [table]
                 where yhbh=t.yhbh and gmsj>t.gmsj)

[水族杰纶]

if not object_id('tb') is null
	drop table tb
Go
Create table tb([yhbh] nvarchar(3),[gmsj] Datetime,[ye] decimal(18,2))
Insert tb
select N'001','2010-09-09',0.90 union all
select N'001','2010-09-10',0.80 union all
select N'001','2010-09-11',0.70 union all
select N'001','2010-09-12',0.60 union all
select N'002','2010-09-09',0.90 union all
select N'002','2010-09-10',0.80
Go
select * 
from [tb] t
where [gmsj]=(select max([gmsj])
            from tb 
            where [yhbh]=t.[yhbh])
yhbh gmsj                    ye
---- ----------------------- ---------------------------------------
002  2010-09-10 00:00:00.000 0.80
001  2010-09-12 00:00:00.000 0.60

[jaydom]

select [yhbh], [gmsj],[ye]
from tb a
where exists (select 1 from tb
              where a.yhbh=yhbh
              group by yhbh
              having MAX(gmsj)=a.gmsj)
              
yhbh	gmsj	ye
001	2010-09-12 00:00:00.000	0.60
002	2010-09-10 00:00:00.000	0.80

[「已注销」]

if not object_id('tb') is null
    drop table tb
Go
Create table tb([yhbh] nvarchar(3),[gmsj] Datetime,[ye] decimal(18,2))
Insert tb
select N'001','2010-09-09',0.90 union all
select N'001','2010-09-10',0.80 union all
select N'001','2010-09-11',0.70 union all
select N'001','2010-09-12',0.60 union all
select N'002','2010-09-09',0.90 union all
select N'002','2010-09-10',0.80
GO

SELECT M.* 
FROM tb M
INNER JOIN (SELECT yhbh, MAX(gmsj)maxDate FROM TB GROUP BY yhbh) N
ON M.yhbh=N.yhbh AND M.gmsj=N.maxDate
ORDER BY M.yhbh

yhbh gmsj                    ye
---- ----------------------- ---------------------------------------
001  2010-09-12 00:00:00.000 0.60
002  2010-09-10 00:00:00.000 0.80

(2 row(s) affected)

[lcqtgb]

select a.yhbh,a.gmsj,b.ye from( 
(select yhbh, max(gmsj)gmsj from tb
group by yhbh) a join (select yhbh ,gmsj, ye from tb) b 

on a.yhbh=b.yhbh and a.gmsj=b.gmsj)

[hhiew]

select *
from
[tb] t
where
gmsj=(select max(gmsj) from [tb] where yhbh=t.yhbh )