SQL case when then 及 isnull() 例子

--> 测试数据：[动态表]
if object_id('[动态表]') is not null drop table [动态表]
go 
create table [动态表](id int,[日期] varchar(15),[原料ID] varchar(20),[原料名称] varchar(20),[成本单价] decimal(20,2))
insert [动态表]
select 1,'2013-11-01','00001','啤酒',10 union all
select 2,'2013-11-15','00001','啤酒',15 union all
select 3,'2013-11-02','00002','饮料',18 union all
select 4,'2013-11-03','00002','饮料',10 union all
select 5,'2013-11-08','00002','饮料',9 

--> 测试数据：[清单表]
if object_id('[清单表]') is not null drop table [清单表]
go 
create table [清单表]([原料ID] varchar(20),[原料名称] varchar(20),[成本单价] decimal(20,2),[期初单价] decimal(20,2))
insert [清单表]
select '00001','啤酒',10 ,15 union all
select '00002','饮料',9 ,18 union all
select '99999','红牛',0 ,20

--测试数据查看
select *

from [清单表] a left join 

(select [原料ID],[成本单价] from [动态表] where id in

(select max(id ) as id from [动态表] where[日期]<= '2013-11-15' group by [原料ID])

) b  on a.[原料ID]=b.[原料ID]
--结果数据 （15,9,20）
select a.[原料ID],a.[原料名称],case when a.[成本单价] <=0 then isnull(b.[成本单价],a.[期初单价]) 

else isnull(b.[成本单价],a.[成本单价]) end as [成本单价]

from [清单表] a left join 

(select [原料ID],[成本单价] from [动态表] where id in

(select max(id ) as id from [动态表] where[日期]<= '2013-11-15' group by [原料ID])

) b  on a.[原料ID]=b.[原料ID]