Question

sql分组后取最大日期的记录

现有表:
create table tempTest
(
f_Spbm varchar(50) not null,--商品编码
f_Rq varchar(50) not null, --日期
f_Kcsl float not null --库存数量
)
go
insert tempTest(f_Spbm,f_Rq,f_Kcsl)
select '1501050049','20130718',22 union
select '1501050049','20130719',21 union
select '1501050049','20130720',20 union
select '1501050049','20130721',19 union
select '1501050049','20130722',18

---------------------------------------------------------------
通过 select * from tempTest 得到
f_Spbm f_Rq f_Kcsl
1501050049 20130718 22.0
1501050049 20130719 21.0
1501050049 20130720 20.0
1501050049 20130721 19.0
1501050049 20130722 18.0
---------------------------------------------------------------
现在我想查到最大日期的商品编码和库存数量
显示结果为(日期不显示):
---------------------------------------------------------------
f_Spbm f_Kcsl
1501050049 18.0
---------------------------------------------------------------
求语句?

Answer 1

select a.f_SPBM,a.f_kcsl
from tempTest a,
(
select f_SPBM,max(f_rq) f_rq
from tempTest
group by f_SPBM
) b
where a. f_SPBM = b. f_SPBM
and a.f_rq = b.f_rq

Answer 2

为什么要分组呢，下面唯罩肢的指世就闷袭可以：
select f_spbm,f_kcsl from test where f_rq=(select max(f_rq) from test);