两表查询，求助

[铛铛]

tb_Type
TypeID
TypeName


tb_Price
PID
TypeID
Price
UpdateTime


要求查询出的列有
TypeID | TypeName | Price | UpdateTime

查询出所有类型最近时间的价格
？？

[闹铃]

select TypeID,TypeName,Price,UpdateTime
from tb_Type inner join tb_Price
on tb_Type.TypeID = tb_Price.TypeID 

[铛铛]

[Quote=引用 1 楼 denghui_li 的回复:]
SQL code


select TypeID,TypeName,Price,UpdateTime
from tb_Type inner join tb_Price
on tb_Type.TypeID = tb_Price.TypeID
[/Quote]
谢谢
我要的是max（UpdateTime）

[闹铃]

select TypeID,min(TypeName),min(Price),max(UpdateTime)
from tb_Type inner join tb_Price
on tb_Type.TypeID = tb_Price.TypeID
group by tb_Type.TypeID 

[铛铛]

[Quote=引用 3 楼 denghui_li 的回复:]
SQL code


select TypeID,min(TypeName),min(Price),max(UpdateTime)
from tb_Type inner join tb_Price
on tb_Type.TypeID = tb_Price.TypeID
group by tb_Type.TypeID
[/Quote]
你这查询出来的是不是一条记录啊？？

[claro]

select pa.TypeID,t.TypeName,pa.Price,pa.UpdateTime
from tb_Price pa
join tb_Type t on pa.Typeid=t.Typeid
where not exists (
select *
from tb_Price pb
where pa.Typeid = pb.Typeid and pa.UpdateTime < pb.UpdateTime)

[claro]

[Quote=引用 6 楼 zxp8819 的回复:]
怎么都不用max函数的
[/Quote]根据你的判断用MAX不合适，因为是根据MAX(UpdateTime)要Price，而不是直接要Price

[ly745455]

MARK

[-晴天]

--得先确定时间范围再查,假设为今年吧:
;with cte as(
select * from tb_Price where updatetime between '2011-01-01' and getdate()
)
select a.TypeID,a.TypeName,b.Price,b.UpdateTime
from tb_Type a inner join cte b on a.typeID=b.typeID
where not exists(select 1 from cte where typeid=a.typeid and Price>b.Price)

--如果是2000,前面cte部分可以直接查询到 #,后面用#来查.
--不应该用max函数,那样更麻烦,在group by,而你的时间又不在group里.
--这里的,typeid=a.typeid 为此类型,price>b.price是此类型里没有比b.price更大的,那b.price就是最大的.

[fsh1985]

select A.TypeID,A.TypeName,B.price,B.UpdateTime from tb_type as A left join (select TypeID,price,UpdateTime from tb_price where convert(varchar(max),TypeID)+convert(varchar(max),MAX(UpdateTime))
in (select TypeID+MAX(UpdateTime) from tb_price group by TypeID )) as B on A.TypeID=B.TypeID

[gogodiy]

select aaa.TypeID,aaa.TypeName,bbb.Price,bbb.UpdateTime from tb_Type as aaa inner join 
(select * from tb_Price as a where not exists (select 1 from tb_Price where TypeID=a.TypeID and UpdateTime>a.UpdateTime)) as bbb
on aaa.TypeID=bbb.TypeID

还可以用cross apply，还可以用row_number() over()函数来做，就不一一写了。

[cd731107]

--给楼主一个使用max（UpdateTime）的方式
select b.TypeID,TypeName,Price,UpdateTime 
from tb_Type a,tb_Price b
where a.TypeID=b.TypeID 
and UpdateTime=(select max(UpdateTime) 
                from tb_Price c 
                 where c.TypeID=a.TypeID)

[liang145]

create table #tb_Type(TypeID int,TypeName nvarchar(10))
 insert #tb_Type
 select 1,'T1' union all
 select 2,'T2' union all
 select 3,'T3' 

create table #tb_Price(PID int, TypeID int,  Price int, UpdateTime datetime)
insert #tb_Price
select 1,1,98,'2011-6-3' union all
select 2,1,45,'2011-7-3' union all
select 3,2,87,'2011-6-3' union all
select 4,3,35,'2011-4-3' union all
select 5,3,46,'2011-8-3' union all
select 6,3,87,'2011-1-3' 

;With T as (select row_number()over(partition by TypeID order by UpdateTime desc) as Num,* from #tb_Price)
select t.TypeID,t.TypeName,p.Price,p.UpdateTime from #tb_Type as t 
join T as p on t.TypeID=p.TypeID where Num=1

[赖哥]

select 字段 FROM 表A JOIN 表B ON A.字段=B.字段 WHERE 条件 END

[铛铛]

[Quote=引用 5 楼 claro 的回复:]
SQL code

select pa.TypeID,t.TypeName,pa.Price,pa.UpdateTime
from tb_Price pa
join tb_Type t on pa.Typeid=t.Typeid
where not exists (
select *
from tb_Price pb
where pa.Typeid = pb.Typeid and ……
[/Quote]
天呐....
怎么都不用max函数的