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 allselect 2,'2013-11-15','00001','啤酒',15 union allselect 3,'2013-11-02','00002','饮料',18 union allselect 4,'2013-11-03','00002','饮料',10 union allselect 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 allselect '00002','饮料',9 ,18 union allselect '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]