表如下
pcode ,pname ,price ,qantity, pdate, price_ill
001 001 10.2 3 2003-05-16 A0305011
001 001 11.2 3 2004-02-22 A0402032
001 001 10.6 3 2005-06-11 A0506008
002 002 23.2 4 2002-05-15 A0205099
002 002 24.8 4 2005-03-13 A0503071
002 002 21.2 4 2005-05-12 A0505046我怎么用一个select 语句 赛选出每个货号(pcode)小与某个时刻,且最接近这个时刻的纪录。如 当 select ..... where pdate<='2005-01-01' 时,选出这两条记录001 001 11.2 3 2004-02-22 A0402032
002 002 23.2 4 2002-05-15 A0205099
pcode ,pname ,price ,qantity, pdate, price_ill
001 001 10.2 3 2003-05-16 A0305011
001 001 11.2 3 2004-02-22 A0402032
001 001 10.6 3 2005-06-11 A0506008
002 002 23.2 4 2002-05-15 A0205099
002 002 24.8 4 2005-03-13 A0503071
002 002 21.2 4 2005-05-12 A0505046我怎么用一个select 语句 赛选出每个货号(pcode)小与某个时刻,且最接近这个时刻的纪录。如 当 select ..... where pdate<='2005-01-01' 时,选出这两条记录001 001 11.2 3 2004-02-22 A0402032
002 002 23.2 4 2002-05-15 A0205099
并不是要最小时间.而是要小于2005-01-01 而且时最接近2005-01-01的那个纪录,
就是在小于2005-01-01的的纪录当中是最大的时间
跟
你贴的结果 不一致,我是按要求来的
wangbao20001766(豹子) 只有一个产品的话,加top可以塞选出来,如果两个以上,就出不来了,你说的是top 1 吗
if exists(select id from sysobjects where id = object_id(N'testtable'))
drop table TestTable
gocreate table TestTable(
pcode nvarchar(20),
pdate datetime,
pother nvarchar(20)
)goinsert into TestTable values('001','2005-02-01','asdf')
insert into TestTable values('001','2005-07-01','asdf')
insert into TestTable values('001','2005-02-02','asdf')
insert into TestTable values('001','2005-01-01','asdf')
insert into TestTable values('001','2004-07-01','asdf')
insert into TestTable values('002','2005-02-01','asdf')
insert into TestTable values('002','2005-07-01','asdf')
insert into TestTable values('003','2005-02-01','asdf')
insert into TestTable values('003','2005-07-01','asdf')
go select * from TestTablegoselect a.*
from testTable a inner join
(select pcode,min(datediff(d,pdate,getdate())) pdatediff
from testtable
where pdate < getdate()
group by pcode) b on a.pcode = b.pcode
where convert(char(10),a.pdate,120) = convert(char(10),dateadd(day, -1*(b.pdatediff),getdate()),120)rollback tran Test050620
FROM [table] a
WHERE (pdate <= '2005-01-01') AND (not EXISTS
(SELECT *
FROM (SELECT *
FROM [table] a
WHERE (pdate <= '2005-01-01') )t
WHERE pcode = a.pcode AND pdate > a.pdate))这个试一下结果 001 11.2 3 2004-02-22 A0402032 002
002 23.2 4 2002-05-15 A0205099
001 2005-07-01 00:00:00.000 asdf
001 2005-02-02 00:00:00.000 asdf
001 2005-01-01 00:00:00.000 asdf
001 2004-07-01 00:00:00.000 asdf
002 2005-02-01 00:00:00.000 asdf
002 2005-07-01 00:00:00.000 asdf
003 2005-02-01 00:00:00.000 asdf
003 2005-07-01 00:00:00.000 asdf
-------------------------------------------------
001 2005-02-02 00:00:00.000 asdf
002 2005-02-01 00:00:00.000 asdf
003 2005-02-01 00:00:00.000 asdf