--生成测试数据 create table saleprice(cmodelcode varchar(10),cmodifydate datetime,fprice int) insert into saleprice select '001','2005-7-1',1000 insert into saleprice select '001','2005-8-1',1400 insert into saleprice select '002','2005-5-1',500 insert into saleprice select '002','2005-8-1',600 insert into saleprice select '002','2005-8-9',650 --执行查询 select a.* from saleprice a where a.fprice = (select max(fprice) from saleprice where cmodelcode=a.cmodelcode) order by a.cmodelcode --输出结果 cmodelcode cmodifydate fprice ---------- ----------- ------- 001 2005-8-1 1400 002 2005-8-9 650
select A.* from saleprice A inner join (select cmodelcode ,max(cmodifydate) as maxcmodifydate from saleprice group by cmodelcode ) B on A.cmodelcode =B.cmodelcode and A.cmodifydate =B.maxcmodifydate
Select * From #saleprice A Where cmodifydate = ( Select max(C.cmodifydate) from #saleprice C where cmodelcode=A.cmodelcode And cmodelcode=A.cmodelcode) Order By cmodelcode
Select * From #saleprice A Where cmodifydate = ( Select max(C.cmodifydate) from #saleprice C where cmodelcode=A.cmodelcode ) Order By cmodelcode
create table saleprice(cmodelcode varchar(10),cmodifydate datetime,fprice int)
insert into saleprice select '001','2005-7-1',1000
insert into saleprice select '001','2005-8-1',1400
insert into saleprice select '002','2005-5-1',500
insert into saleprice select '002','2005-8-1',600
insert into saleprice select '002','2005-8-9',650
--执行查询
select
a.*
from
saleprice a
where
a.fprice = (select max(fprice) from saleprice where cmodelcode=a.cmodelcode)
order by
a.cmodelcode
--输出结果
cmodelcode cmodifydate fprice
---------- ----------- -------
001 2005-8-1 1400
002 2005-8-9 650
(select cmodelcode ,max(cmodifydate) as maxcmodifydate from saleprice group by cmodelcode ) B on A.cmodelcode =B.cmodelcode and A.cmodifydate =B.maxcmodifydate
Where cmodifydate = ( Select max(C.cmodifydate)
from #saleprice C
where cmodelcode=A.cmodelcode And cmodelcode=A.cmodelcode)
Order By cmodelcode
Select * From #saleprice A
Where cmodifydate = ( Select max(C.cmodifydate)
from #saleprice C
where cmodelcode=A.cmodelcode )
Order By cmodelcode