item nuber,price,date select itemnumber,price,date from 表 where date=max(date) group by itemnumber,price,date
我们不能确定很准确的日期,我们要在以item number分组里,取到最近的价格单。
create table #t(itemnumber varchar(10),price decimal(10,2), date smalldatetime ) insert into #t select 'a1',10.4,'2004-10-24' union select 'a1',10.7,'2005-07-17' select * from #t select top 1 itemnumber,price,date from #t group by date,itemnumber,price order by date desc drop table #t ------------------------------------- /* 过滤前的数据: itemnumber price date a1 10.40 2004-10-24 00:00:00 a1 10.70 2005-07-17 00:00:00 过滤后的数据:itemnumber price date a1 10.70 2005-07-17 00:00:00 */
yes.but I can't use max function in our system.we use SQL language,not be SQL server.so you can find better solutions without "MAX" .thank you .
select a.* from tablename a,(select itemnumber,max(date) as [date] from tablename group by itemnumber) b where a.itemnumber=b.itemnumber and a.[date]=b.[date]
select price from table where date = (select max(date) from table)
select itemnumber,price,date from 表
where date=max(date) group by itemnumber,price,date
date smalldatetime )
insert into #t
select 'a1',10.4,'2004-10-24' union
select 'a1',10.7,'2005-07-17'
select * from #t
select top 1 itemnumber,price,date from #t
group by date,itemnumber,price order by date desc
drop table #t
-------------------------------------
/*
过滤前的数据:
itemnumber price date
a1 10.40 2004-10-24 00:00:00
a1 10.70 2005-07-17 00:00:00
过滤后的数据:itemnumber price date
a1 10.70 2005-07-17 00:00:00
*/
from tablename a,(select itemnumber,max(date) as [date] from tablename group by itemnumber) b
where a.itemnumber=b.itemnumber and a.[date]=b.[date]
where date = (select max(date) from table)