27,579
社区成员
发帖
与我相关
我的任务
分享
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)
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)
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 *
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
select *
from [table] t
where not exists(select 1
from [table]
where yhbh=t.yhbh and gmsj>t.gmsj)