查询本次采购和上次采购价格,没有则不显示(升级版)

Question

tableA(采购清单)
计划ID     物资代码    物资名称       采购数量
200901     101         显示器          2
200902     102         键盘            2
200903     103         鼠标            2tableB(比价表)
比价表ID     物资代码      计划ID           日期
B0901          101         200901          2009-12-01
B0902          102         200902          2009-12-01
B0903          103         200903          2009-12-01
B0801          101         200801          2008-10-01
B0802          102         200802          2008-08-01
B0701          101         200701          2007-01-01tableC(历史价格记录表)
        价格       日期            比价表ID        1000    2009-12-01         B0901
        500     2009-12-01         B0902
        60      2009-12-01         B0903
        900     2008-10-01         B0801
        400     2008-08-01         B0802
        800     2007-01-01          B0701想要的结果:
物资名称     采购数量     上次价格     上次时间     本次价格
显示器       2            900          2008-10-01    1000
键盘         2            400          2008-08-01    500
鼠标         2            无            无           60

Answer 1

tableC(历史价格记录表)
少一重要字段“物资代码”

Answer 2

表C里没有物资代码,只有比价表的ID号.原数据库就是这么设计的.所以个人感觉有难度.不知道在B表中如何查到与本次采购物资一样的物资.

Answer 3

select 
  a.物资名称,a.采购数量,isnull(cast(c1.价格 as varchar(30)),'无') as 上次价格,isnull(convert(varchar(30),c1.日期,120),'无') as 上次时间,c.价格 as 本次价格 
from tableA a inner join tableB b
on a.计划ID=b.计划ID and a.物资代码 = b.物资代码
inner join tableC c
on b.比价表ID  = c.比价表ID 
left join tableC c1
on b.物资代码 = c1.物资代码 and c1.日期 = select max(日期) from tableC where 物资代码 = b.物资代码 and 日期<b.日期)

Answer 4

---测试数据---
if object_id('[tableA]') is not null drop table [tableA]
go
create table [tableA]([计划ID] int,[物资代码] int,[物资名称] varchar(6),[采购数量] int)
insert [tableA]
select 200901,101,'显示器',2 union all
select 200902,102,'键盘',2 union all
select 200903,103,'鼠标',2
if object_id('[tableB]') is not null drop table [tableB]
go
create table [tableB]([比价表ID] varchar(5),[物资代码] int,[计划ID] int,[日期] datetime)
insert [tableB]
select 'B0901',101,200901,'2009-12-01' union all
select 'B0902',102,200902,'2009-12-01' union all
select 'B0903',103,200903,'2009-12-01' union all
select 'B0801',101,200801,'2008-10-01' union all
select 'B0802',102,200802,'2008-08-01' union all
select 'B0701',101,200701,'2007-01-01'
if object_id('[tableC]') is not null drop table [tableC]
go
create table [tableC]([价格] int,[日期] datetime,[比价表ID] varchar(5))
insert [tableC]
select 1000,'2009-12-01','B0901' union all
select 500,'2009-12-01','B0902' union all
select 60,'2009-12-01','B0903' union all
select 900,'2008-10-01','B0801' union all
select 400,'2008-08-01','B0802' union all
select 800,'2007-01-01','B0701'
 
---查询---
select a.物资名称,a.采购数量,
上次价格=isnull((select top 1 ltrim(t2.价格) from tableb t1,tablec t2 where t1.比价表ID=t2.比价表ID and t1.物资代码=a.物资代码 and t2.日期<c.日期 order by t2.日期 desc),'无'),
上次时间=isnull((select top 1 convert(varchar(10),t2.日期,120) from tableb t1,tablec t2 where t1.比价表ID=t2.比价表ID and t1.物资代码=a.物资代码 and t2.日期<c.日期 order by t2.日期 desc),'无'),    
本次价格=c.价格
from tablea a
join tableb b on a.物资代码=b.物资代码 and a.计划ID=b.计划ID
join tablec c on b.比价表ID=c.比价表ID---结果---
物资名称   采购数量        上次价格         上次时间       本次价格        
------ ----------- ------------ ---------- ----------- 
显示器    2           900          2008-10-01 1000
键盘     2           400          2008-08-01 500
鼠标     2           无            无          60（所影响的行数为 3 行）

Answer 5

那要从tableB取？
select 
  a.物资名称,a.采购数量,isnull(cast(c1.价格 as varchar(30)),'无') as 上次价格,isnull(convert(varchar(30),c1.日期,120),'无') as 上次时间,c.价格 as 本次价格 
from tableA a inner join tableB b
on a.计划ID=b.计划ID and a.物资代码 = b.物资代码
inner join tableC c
on b.比价表ID  = c.比价表ID 
left join tableC c1
on c1.日期 = (select max(日期) from tableC x,tableB y where y.物资代码 = b.物资代码 and x.日期<b.日期 and y.比价表ID = x.比价表ID )

Answer 6

谢谢大家,我采用了JOSY的答案,学到不少知识.这里的人都很热情.