国粹经典 發表於 2025-10-21 11:49:25

SQL中的CASE WHEN用法常用场景分析

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、简单CASE WHEN函数：</a></li><li><a href="#_label1">二、CASE WHEN条件表达式函数</a></li><li><a href="#_label2">三、常用场景</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_0">场景1：不同状态展示为不同的值</a></li><li><a href="#_lab2_2_1">场景2：统计不同状态下的值</a></li><li><a href="#_lab2_2_2">场景3：配合聚合函数做统计</a></li><li><a href="#_lab2_2_3">场景4：CASE WHEN中使用子查询</a></li><li><a href="#_lab2_2_4">场景5：经典行转列，结合max聚合函数</a></li></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>一、简单CASE WHEN函数：</h2>
<div class="jb51code"><pre class="brush:sql;">CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
# 使用 IF 函数进行替换
IF(SCORE = 'A', '优', '不及格')</pre></div>
<p><strong>THEN后边的值与ELSE后边的值类型应一致，否则会报错</strong>。<br />如下：<br />CASE SCORE WHEN &lsquo;A&rsquo; THEN &lsquo;优&rsquo; ELSE 0 END&rsquo;优&rsquo;和0数据类型不一致则报错：<br /> ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER</p>
<p>简单CASE WHEN函数只能应对一些简单的业务场景，而CASE WHEN条件表达式的写法则更加灵活。</p>
<p class="maodian"><a name="_label1"></a></p><h2>二、CASE WHEN条件表达式函数</h2>
<p>类似JAVA中的IF ELSE语句。</p>
<p>格式：</p>
<div class="jb51code"><pre class="brush:sql;">CASE WHEN condition THEN result

ELSE result
END</pre></div>
<p>SQL语言演示：</p>
<div class="jb51code"><pre class="brush:sql;">CASE
       WHEN SCORE = 'A' THEN '优'
   WHEN SCORE = 'B' THEN '良'
   WHEN SCORE = 'C' THEN '中'
   ELSE '不及格' END
# 等同于
CASE score
    WHEN 'A' THEN '优'
    WHEN 'B' THEN '良'
    WHEN 'C' THEN '中'
    ELSE '不及格' END</pre></div>
<p>condition是一个返回布尔类型的表达式，<br />如果表达式返回true，则整个函数返回相应result的值，<br />如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。</p>
<p class="maodian"><a name="_label2"></a></p><h2>三、常用场景</h2>
<blockquote><p>前言</p></blockquote>
<p><strong>students表的DDL</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- auto-generated definition
create table students
(
    stu_codevarchar(10) null,
    stu_namevarchar(10) null,
    stu_sex   int         null,
    stu_score int         null
);</pre></div>
<p><strong>students表的DML</strong></p>
<div class="jb51code"><pre class="brush:sql;"># 其中stu_sex字段，0表示男生，1表示女生。
INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xm', '小明', 0, 88);
INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xl', '夏磊', 0, 55);
INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xf', '晓峰', 0, 45);
INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xh', '小红', 1, 89);
INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xn', '小妮', 1, 77);
INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xy', '小一', 1, 99);
INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xs', '小时', 1, 45);
</pre></div>
<p><strong>energy_test表的DDL</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- auto-generated definition
create table energy_test
(
    e_codevarchar(2)    null,
    e_value decimal(5, 2) null,
    e_typeint         null
);
</pre></div>
<p><strong>energy_test表的DML</strong></p>
<div class="jb51code"><pre class="brush:sql;"># 其中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗
INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('北京', 28.50, 0);
INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('北京', 23.50, 1);
INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('北京', 28.12, 2);
INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('北京', 12.30, 0);
INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('北京', 15.46, 1);
INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('上海', 18.88, 0);
INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('上海', 16.66, 1);
INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('上海', 19.99, 0);
INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('上海', 10.05, 0);
</pre></div>
<p><strong>p_price表的DDL</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- auto-generated definition
create table p_price
(
    p_price decimal(5, 2) null comment '价格',
    p_level int         null comment '等级',
    p_limit int         null comment '阈值'
)
    comment '电能耗单价表';
</pre></div>
<p><strong>p_price表的DML</strong></p>
<div class="jb51code"><pre class="brush:sql;">INSERT INTO test.p_price (p_price, p_level, p_limit) VALUES (1.20, 0, 10);
INSERT INTO test.p_price (p_price, p_level, p_limit) VALUES (1.70, 1, 30);
INSERT INTO test.p_price (p_price, p_level, p_limit) VALUES (2.50, 2, 50);
</pre></div>
<p><strong>user_col_comments 表的DDL</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- auto-generated definition
create table user_col_comments
(
    column_name varchar(50)null comment '列名',
    comment   varchar(100) null comment '列的备注'
);
</pre></div>
<p><strong>user_col_comments 表的DML</strong></p>
<div class="jb51code"><pre class="brush:sql;">INSERT INTO test.user_col_comments (column_name, comment) VALUES ('SHI_SHI_CODE', '设施编号');
INSERT INTO test.user_col_comments (column_name, comment) VALUES ('SHUI_HAO', '水耗');
INSERT INTO test.user_col_comments (column_name, comment) VALUES ('RE_HAO', '热耗');
INSERT INTO test.user_col_comments (column_name, comment) VALUES ('YAN_HAO', '盐耗');
INSERT INTO test.user_col_comments (column_name, comment) VALUES ('OTHER', '其他');
</pre></div>
<p class="maodian"><a name="_lab2_2_0"></a></p><h3>场景1：不同状态展示为不同的值</h3>
<p>有分数score，score&lt;60返回不及格，score&gt;=60返回及格，score&gt;=80返回优秀</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202510/2025102111452217.png" /></p>
<div class="jb51code"><pre class="brush:sql;"># 有分数score，score&lt;60返回不及格，score&gt;=60返回及格，score&gt;=80返回优秀
SELECT
    stu_name,
    (CASE WHEN stu_score &lt; 60 THEN '不及格'
      WHEN stu_score &gt;= 60 AND stu_score &lt; 80 THEN '及格'
      WHEN stu_score &gt;= 80 THEN '优秀'
      ELSE '异常' END) AS REMARK
FROM students;
</pre></div>
<p><strong>注意：如果你想判断score是否null的情况，WHEN score = null THEN &lsquo;缺席考试&rsquo;，这是一种错误的写法，正确的写法应为：</strong><br /><code>CASE WHEN score IS NULL THEN &#39;缺席考试&#39; ELSE &#39;正常&#39; END</code></p>
<p class="maodian"><a name="_lab2_2_1"></a></p><h3>场景2：统计不同状态下的值</h3>
<p>现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出结果。其中stu_sex字段，0表示男生，1表示女生。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202510/2025102111452217.png" /></p>
<div class="jb51code"><pre class="brush:sql;">SELECT
        sum(CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
        sum(CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
        sum(CASE WHEN STU_SCORE &gt;= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
        sum(CASE WHEN STU_SCORE &gt;= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM
        students;
</pre></div>
<p>输出结果如下：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202510/2025102111452225.png" /></p>
<p>注意点：</p>
<ul><li>用的是 ：<code>sum?</code>而不是count</li><li><code>THEN 1 ELSE 0</code>的位置不能改变：否则会有以下效果：</li></ul>
<div class="jb51code"><pre class="brush:sql;">sum(CASE WHEN stu_sex = 0 THEN '1' ELSE '0' END) AS '男性',
改变了
sum(CASE WHEN stu_sex = 0 THEN '0' ELSE '1' END) AS '女性'：</pre></div>
<ul><li>字符 &lsquo;0&rsquo; 和 数值 0，使用 都是一样的</li></ul>
<p class="maodian"><a name="_lab2_2_2"></a></p><h3>场景3：配合聚合函数做统计</h3>
<p>现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果<br />有能耗表如下：其中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202510/2025102111452255.png" /></p>
<div class="jb51code"><pre class="brush:sql;">select e_code,
       sum(case when e_type = 0 then e_value else 0 end) as '水耗',
       sum(case when e_type = 1 then e_value else 0 end) as '电耗',
       sum(case when e_type = 2 then e_value else 0 end) as '热耗'
from energy_test
group by e_code;
</pre></div>
<p>输出结果如下：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202510/2025102111452291.png" /></p>
<p class="maodian"><a name="_lab2_2_3"></a></p><h3>场景4：CASE WHEN中使用子查询</h3>
<p>根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。<br />当能耗值小于10时，使用P_LEVEL=0时的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1时的P_PRICE的值&hellip;</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202510/2025102111452260.png" /></p>
<p>energy_test 我修改了e_type 为1的值的两条数据的e_value。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202510/2025102111452246.png" /></p>
<div class="jb51code"><pre class="brush:sql;">select e_code, e_value,
   (CASE WHEN e_value &lt;= (SELECT p_limit FROM p_price WHERE p_level = 0)
      THEN (SELECT p_price FROM p_price WHERE p_level = 0)
   WHEN e_value &gt; (SELECT p_limit FROM p_price WHERE p_level = 0) AND e_value &lt;= (SELECT p_limit FROM p_price WHERE p_level = 1)
      THEN (SELECT P_PRICE FROM p_price WHERE P_LEVEL = 1)
   WHEN e_value &gt; (SELECT p_limit FROM p_price WHERE p_level = 1) AND e_value &lt;= (SELECT p_limit FROM p_price WHERE p_level = 2)
      THEN (SELECT p_price FROM p_price WHERE P_LEVEL = 2) end ) as price
from energy_test
where e_type = 1;
</pre></div>
<p>输出结果如下：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202510/2025102111452298.png" /></p>
<p class="maodian"><a name="_lab2_2_4"></a></p><h3>场景5：经典行转列，结合max聚合函数</h3>
<p>行转列中 SUM作用：无用，但是select后得跟聚合函数，不能去掉sum。直接写max或者min也行。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202510/2025102111452290.png" /></p>
<div class="jb51code"><pre class="brush:sql;">select
    max(case when column_name = 'SHI_SHI_CODE' then comment else ''end) as SHI_SHI_CODE_COMMENT,
    max(case when column_name = 'SHUI_HAO' then comment else ''end) as SHUI_HAO_COMMENT,
    max(case when column_name = 'RE_HAO' then comment else ''end) as RE_HAO_COMMENT,
    max(case when column_name = 'YAN_HAO' then comment else ''end) as YAN_HAO_COMMENT,
    max(case when column_name = 'OTHER' then comment else '' end) as OTHER_COMMENT
from user_col_comments;
</pre></div>
<p>输出结果如下：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202510/2025102111452298.png" /></p>
頁: [1]
查看完整版本: SQL中的CASE WHEN用法常用场景分析