sql 查询语句不会写，帮帮忙，谢谢！
表a
编码 名称
1 苹果
2 梨
表b
编码 数量 价格
1 20 5
2 10 6
1 10 5.1
1 5 5.2
2 30 5.8
查询结果
编码 名称 数量 上次价格 最新价格
1 苹果 5 5.1 5.2
2 梨 30 6 5.8
语句应该如何写，谢谢了！
[解决办法]
if object_id('[TBA]') is not null drop table [TBA]
go
create table [TBA] (编码 int,名称 nvarchar(4))
insert into [TBA]
select 1,'苹果' union all
select 2,'梨'
select * from [TBA]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (编码 int,数量 int,价格 numeric(2,1))
insert into [TB]
select 1,20,5 union all
select 2,10,6 union all
select 1,10,5.1 union all
select 1,5,5.2 union all
select 2,30,5.8
select * from [TB]
SELECT A.编码,
(SELECT 名称 FROM TBA WHERE TBA.编码=A.编码) AS 名称,
A.数量 ,
上次价格 = ( SELECT 价格
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY 编码 ORDER BY num DESC ) AS NumN ,
*
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY GETDATE() ) AS num ,
*
FROM dbo.TB
) TT
) T
WHERE T.numn = 2
AND T.编码 = A.编码
) ,
A.价格 AS 最新价格
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY GETDATE() ) AS num ,
*
FROM dbo.TB
) A
WHERE NOT EXISTS ( SELECT 1
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY GETDATE() ) AS num ,
*
FROM dbo.TB
) B
WHERE B.编码 = A.编码
AND A.num < b.num )
/*
编码名称数量上次价格最新价格
1苹果55.15.2
2梨306.05.8*/