with t as
(select a.id,a.name,b.pDate,b.price,
row_number() over(partition by a.id order by b.pDate) rn 
from [product] a
inner join [price] b on a.id=b.pId 
where b.pDate>'[变量今天]'
)
select id,name,pDate,price
from t where rn=1

------解决方案--------------------
SQL code
select id,name,pDate,price from [product] 
inner join [price] on id=pId 
where datepart(dw,pDate)-datepart(dw,getdate())=1

------解决方案--------------------
SQL code

with test as
(
select a.id,a.name,b.pDate,b.price,
row_number() over( order by b.pDate) rownum
from project a 
inner join price b on a.id = b.pid
where b.pDate >'[变量今天]'
)
select top 1 * from test

------解决方案--------------------

SQL code


go
if object_id('tbl')is not null
drop table tbl
go
create table tbl(
[id] varchar(10),
[name] varchar(10),
pDate datetime,
price numeric(5,2)
)
go
insert tbl
select '1001','茄子','2012-01-25',2.5 union all
select '1001','茄子','2012-02-02',2.8 union all
select '1001','茄子','2012-03-06',2.2 union all
select '1002','萝卜','2012-02-24',1.5 union all
select '1002','萝卜','2012-02-19',1.2 union all
select '1002','萝卜','2012-03-01',1.3 union all
select '1003','西红柿','2012-01-25',3.2 union all
select '1003','西红柿','2012-02-25',3.0 union all
select '1003','西红柿','2012-03-06',2.9 union all
select '1004','土豆','2012-02-25',1.8 union all
select '1004','土豆','2012-02-28',1.5 union all
select '1004','土豆','2012-02-16',1.7


select * from tbl a
where pDate=(select max(pdate) from tbl b where pdate<=getdate() 
and a.[id]=b.[id]) order by [id] asc
/*
id  name  pDate  price 
1001    茄子    2012-03-06 00:00:00.000    2.20
1002    萝卜    2012-03-01 00:00:00.000    1.30
1003    西红柿    2012-03-06 00:00:00.000    2.90
1004    土豆    2012-02-28 00:00:00.000    1.50

*/