SQL 中 CASE WHEN 及 SELECT CASE WHEN 的用法详解
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">SQL 中 CASE WHEN 及 SELECT CASE WHEN 的用法</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1. 基本语法结构</a></li><li><a href="#_lab2_0_1">2. 在 SELECT 语句中的用法</a></li><ul class="third_class_ul"><li><a href="#_label3_0_1_0">示例1：简单条件判断</a></li><li><a href="#_label3_0_1_1">示例2：等值比较（简单形式）</a></li></ul><li><a href="#_lab2_0_2">3. 在 UPDATE 语句中的用法</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_0_3">4. 在 ORDER BY 中的用法</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_0_4">5. 在 GROUP BY 中的用法</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_0_5">6. 嵌套 CASE WHEN 用法</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_0_6">7. 在聚合函数中使用</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_0_7">注意事项</a></li><ul class="third_class_ul"></ul></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>SQL 中 CASE WHEN 及 SELECT CASE WHEN 的用法</h2><p><code>CASE WHEN</code> 是 SQL 中非常实用的条件表达式，它允许你在查询中实现条件逻辑。以下是详细的用法说明：</p>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1. 基本语法结构</h3>
<div class="jb51code"><pre class="brush:sql;">CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END</pre></div>
<p>或者简单形式：</p>
<div class="jb51code"><pre class="brush:sql;">CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END</pre></div>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2. 在 SELECT 语句中的用法</h3>
<p class="maodian"><a name="_label3_0_1_0"></a></p><h4>示例1：简单条件判断</h4>
<div class="jb51code"><pre class="brush:sql;">SELECT
employee_name,
salary,
CASE
WHEN salary > 10000 THEN '高薪'
WHEN salary > 5000 THEN '中等'
ELSE '一般'
END AS salary_level
FROM employees;</pre></div>
<p class="maodian"><a name="_label3_0_1_1"></a></p><h4>示例2：等值比较（简单形式）</h4>
<div class="jb51code"><pre class="brush:sql;">SELECT
product_name,
CASE category_id
WHEN 1 THEN '电子产品'
WHEN 2 THEN '服装'
WHEN 3 THEN '食品'
ELSE '其他'
END AS category_name
FROM products;</pre></div>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>3. 在 UPDATE 语句中的用法</h3>
<div class="jb51code"><pre class="brush:sql;">UPDATE orders
SET discount =
CASE
WHEN order_amount > 1000 THEN 0.1
WHEN order_amount > 500 THEN 0.05
ELSE 0
END;
</pre></div>
<p class="maodian"><a name="_lab2_0_3"></a></p><h3>4. 在 ORDER BY 中的用法</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM customers
ORDER BY
CASE
WHEN country = 'China' THEN 1
WHEN country = 'USA' THEN 2
ELSE 3
END;
</pre></div>
<p class="maodian"><a name="_lab2_0_4"></a></p><h3>5. 在 GROUP BY 中的用法</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
CASE
WHEN age < 20 THEN '青少年'
WHEN age BETWEEN 20 AND 30 THEN '青年'
ELSE '成年'
END AS age_group,
COUNT(*) AS count
FROM users
GROUP BY age_group;
</pre></div>
<p class="maodian"><a name="_lab2_0_5"></a></p><h3>6. 嵌套 CASE WHEN 用法</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
student_name,
score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN
CASE
WHEN attendance_rate > 0.9 THEN 'B+'
ELSE 'B'
END
WHEN score >= 70 THEN 'C'
ELSE 'D'
END AS grade
FROM students;
</pre></div>
<p class="maodian"><a name="_lab2_0_6"></a></p><h3>7. 在聚合函数中使用</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
department_id,
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department_id;
</pre></div>
<p class="maodian"><a name="_lab2_0_7"></a></p><h3>注意事项</h3>
<ol><li>CASE WHEN 会按顺序判断条件，第一个满足的条件会被执行，后面的条件将被忽略</li><li>如果没有条件满足且没有 ELSE 子句，结果将为 NULL</li><li>所有 THEN 子句返回的数据类型应该兼容</li><li>在复杂的 CASE 表达式中，适当使用括号可以提高可读性</li></ol>
<p>CASE WHEN 是 SQL 中非常强大的条件表达式，掌握它可以让你写出更加灵活和强大的查询语句。</p>
頁:
[1]