SQL--Case When.. Then.. end的使用

Case  When.. Then.. end的使用场景

当字段有不同的值，根据不同的值表示不同的内容

use [数据库名]     
go
if exists( select * from sys.views where name='v_KAR_Schedule_Report')  --判断视图是否已存在
drop view v_KAR_Schedule_Report      --存在则删除视图
go

Create view  [dbo].[v_KAR_Schedule_Report]    --创建视图
as
select Draft_Number as DraftNO,Schedule_Approval_Date as ScheduleApprovalDate,Engagement_No as EngagementNO,Schedule_ID as ScheduleID,Schedule_Created as ScheduleCreated,Schedule_Currency as ScheduleCurrency,Schedule_Total_InclVAT as ScheduleTotalInclVAT,Schedule_Fee as ScheduleFee,
       Schedule_Expense as ScheduleExpense ,Net_Total_ExclVAT as NetTotalExclVAT,Allocated_Total_ExclVAT as AllocatedTotalExclVAT ,Schedule_Narrative as ScheduleNarrative,
--        case  字段 when 初始值 then 要展示的值 else 剩余情况要表示的值 end  as 新字段名
--
       case Hardcopy_Preference when 'A' then 'Softcopy only' when 'B' then 'Softcopy & hardcopy (centrally dispatch to client)' when 'C' then 'Softcopy & hardcopy (EM to collect and deliver)' else '' end as HardcopyPreference,   --使用场景
       isnull(Is_Cover_Required,'') as IsCoverRequired, -- 空值(null)的判断  为null则表示为空字符串
Invoice_To_EntityNo as InvoiceToEntityNO,Invoice_To_ContactNo as InvoiceToContactNO,Invoice_To_APContactNo as InvoiceToAPContactNO,Draft_Number_Create_Time as DraftNOCreateTime
from EM_Schedule
go

 

select a.* ,(case a.status when 0 then '正常' when 1 then '删除' else a.status end) as status from
(select *,cast(status as varchar(64)) status from tb) a;