/*
    人员：LDH
    功能：SQL-Case When用法
    日期：2021-5-14
 */
 
USE TestDB;
 GO
 
CREATE TABLE Employee
    (
      EmpId INT IDENTITY(20210001, 1)
                PRIMARY KEY ,
      EmpName NVARCHAR(20) NOT NULL ,
      EmpAge INT CHECK ( EmpAge BETWEEN 1 AND 150 )
                 DEFAULT 1 ,
      EmpStatus BIT NOT NULL
                    DEFAULT 0 --0:在职 1-离职
                    
    );
    
INSERT  INTO dbo.Employee
        ( EmpName, EmpAge, EmpStatus )
VALUES  ( N‘Jason‘, 31, 0 ),
        ( N‘Tom‘, 18, 1 ),
        ( N‘Lucy‘, 21, 1 ),
        ( N‘Jack‘, 31, 0 ),
        ( N‘Lily‘, 18, 1 ),
        ( N‘Frank‘, 21, 0 ),
        ( N‘Bush‘, 51, 0 ),
        ( N‘Apple‘, 18, 1 ),
        ( N‘Mack‘, 21, 0 ),
        ( N‘Black‘, 28, 1 ),
        ( N‘Pear‘, 18, 0 ),
        ( N‘Banana‘, 21, 0 );
        
SELECT  EmpId ,
        EmpName ,
        EmpAge ,
        EmpStatus
FROM    dbo.Employee;

-- =============================
-- 方法1
-- =============================

SELECT  EmpId ,
        EmpName ,
        EmpAge ,
        ‘EmpStatus‘ = CASE WHEN EmpStatus = 0 THEN ‘在职‘
                           WHEN EmpStatus = 1 THEN ‘离职‘
                      END
FROM    dbo.Employee;


-- =============================
-- 方法2
-- =============================
SELECT  EmpId ,
        EmpName ,
        EmpAge ,
        ‘EmpStatus‘ = CASE EmpStatus
                        WHEN 0 THEN ‘在职‘
                        WHEN 1 THEN ‘离职‘
                      END
FROM    dbo.Employee;