SQL之CASE WHEN函数运用

2014-01-09  来源:我要手册网  

在项目中,有些时候需要联表并计算两字段的值,这个时候CASE WHEN函数就发挥了其作用。废话不多说,请看如下SQL语言:

select c.ProductID,(CASE WHEN (select top 1 Price from Pro_Norm as d where d.Pro_ID=c.ProductID)!='' THEN (CASE WHEN c.ProModel=1 THEN (select top 1 Price from Pro_Norm as d where d.Pro_ID=c.ProductID)*c.ProCredit ELSE (select top 1 Price from Pro_Norm as d where d.Pro_ID=c.ProductID)-c.ProCredit END) ELSE c.ProPrice END) as ProPrice from Pro_Rel_Mot as c

select a.*,(CASE WHEN b.ProPrice!='' THEN b.ProPrice ELSE a.pro_ShopPrice END) as EndProPrice from yxs_product as a left join (select c.ProductID,(CASE WHEN (select top 1 Price from Pro_Norm as d where d.Pro_ID=c.ProductID)!='' THEN (CASE WHEN c.ProModel=1 THEN (select top 1 Price from Pro_Norm as d where d.Pro_ID=c.ProductID)*c.ProCredit ELSE (select top 1 Price from Pro_Norm as d where d.Pro_ID=c.ProductID)-c.ProCredit END) ELSE c.ProPrice END) as ProPrice from Pro_Rel_Mot as c) as b on a.pro_ID = b.ProductID where a.pro_Audit=1 and datediff(DAY,a.pro_AutoUp,getdate()) >=0 and datediff(DAY,a.pro_AutoDown,getdate()) <= 0 and cid=90 order by EndProPrice desc

[本站声明] 本网站部分文章来源于网络,如有侵权请来邮([email protected])告知,本网站将进行更正处理。
分享:
  • 相关阅读
热门标签: sql  casewhen  case  when
推荐文章
热门文章