1. 基本的CASE WHEN语句: 基本的CASE WHEN语句用于根据条件执行不同的操作。它的语法如下:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE result END
示例:
SELECT column1, column2, CASE WHEN column1 > 10 THEN 'Large' WHEN column1 <= 10 THEN 'Small' ELSE 'Unknown' END AS size_category FROM mytable;
2. CASE WHEN语句中的聚合函数: CASE WHEN语句还可以在聚合函数中使用,以根据条件对聚合结果进行分类。示例:
SELECT category, COUNT(*) AS count, SUM(CASE WHEN price > 100 THEN 1 ELSE 0 END) AS expensive_count, SUM(CASE WHEN price <= 100 THEN 1 ELSE 0 END) AS affordable_count FROM products GROUP BY category;
3. CASE WHEN语句中的子查询: 可以在CASE WHEN语句中使用子查询,以根据子查询的结果执行不同的操作。示例:
SELECT column1, column2, CASE WHEN column1 IN (SELECT id FROM othertable) THEN 'Found' ELSE 'Not Found' END AS status FROM mytable;