SQL CASE

SQL CASE 用于在SQL语句中提供条件逻辑(if-else)。

SQL CASE 语法

有两种SQL CASE语法:简单表达和搜索表达。

简单CASE 表达:

CASE 列1
        WHEN 条件1 THEN 结果1
        WHEN 条件2 THEN 结果2
        ELSE 结果3
        END AS 新列名

例如:

SELECT CASE Gender
           WHEN 'M' THEN '男'
           WHEN 'F' THEN '女'
           END AS Gender

搜索CASE 表达:

CASE 
        WHEN 条件1 THEN 结果1
        WHEN 条件2 THEN 结果2
        ELSE 结果3
        END AS 新列名

例如:

SELECT CASE 
           WHEN Gender='M' THEN '男'
           WHEN Gender='F' THEN '女'
           END AS Gender

更多 SQL CASE 范例

数据表: Employees

EmployeeId FirstName LastName Department Salary
1 Hudyaf Yufda F 100
2 Qiav Vofa F 120
3 Pofaq Rqafda F 110
4 Bldfa Mifeaa F 80
5 Opfae Qzhtya D 200
6 Xuggg Reia D 100
7 Kofyu Hhg O 120

SELECT FirstName, LastName,
           Department = CASE Department
           WHEN 'F' THEN 'Financial'
           WHEN 'D' THEN 'Development'
           ELSE 'Other'
           END
FROM Employees

结果类似于:

FirstName LastName Department
Hudyaf Yufda Financial
Qiav Vofa Financial
Pofaq Rqafda Financial
Bldfa Mifeaa Financial
Opfae Qzhtya Development
Xuggg Reia Development
Kofyu Hhg Other

UPDATE Employees
SET Salary = 
      ( CASE Department
            WHEN 'D' THEN Salary + 10
            WHEN 'F' THEN Salary + 11
            ELSE Salary + 12
        END
      )

更新后的 Employees 表会类似于:

EmployeeId FirstName LastName Department Salary
1 Hudyaf Yufda F 111
2 Qiav Vofa F 131
3 Pofaq Rqafda F 121
4 Bldfa Mifeaa F 91
5 Opfae Qzhtya D 210
6 Xuggg Reia D 110
7 Kofyu Hhg O 132