Oracle SQL中能否结合With子句用IF子句？求解决重复行问题

我有一张school表，结构及数据如下：

CREATE TABLE school (
    classroom varchar(125),
    girls int,
    boys int,
    sum_class int
);

INSERT INTO school
    (classroom, girls, boys, sum_class)
    values('1a',4,10,14);

INSERT INTO school
    (classroom, girls, boys, sum_class)
    values('1b',11,19,30);

INSERT INTO school
    (classroom, girls, boys, sum_class)
    values('2a',12,13,25);

INSERT INTO school
    (classroom, girls, boys, sum_class)
    values('2b',10,9,19);

后续该表会自动新增数据。已知部分班级（如'2c'、'2d'）暂未录入，我编写了如下SQL查询：

With exact_class AS (
 SELECT '2c' AS classroom, 0 AS girls, 0 AS boys, 0 AS sum_class FROM dual
  UNION
 SELECT '2d' AS classroom, 0 AS girls, 0 AS boys, 0 AS sum_class FROM dual
)

SELECT classroom, girls, boys, sum_class 
FROM school
UNION
SELECT * FROM exact_class

该查询可在目标班级数据录入前显示其0值行，但当'2c'等班级的数据（如('2c',6,14,20)）录入后，查询结果会出现重复行：

'2c',6,14,20
'2c',0,0,0

我需要仅显示有效行：无数据时显示0值，有数据时显示实际值。请问能否在该查询中使用IF子句实现逻辑切换？我尝试用IF子句但报错，是否有简单解决方案？

不需要用IF子句，更简洁高效的方式是通过左连接（LEFT JOIN）+ COALESCE函数实现需求：

基础版（仅显示预设的未录入班级） #

WITH exact_class AS (
 SELECT '2c' AS classroom FROM dual
 UNION
 SELECT '2d' AS classroom FROM dual
)
SELECT 
    ec.classroom,
    COALESCE(s.girls, 0) AS girls,
    COALESCE(s.boys, 0) AS boys,
    COALESCE(s.sum_class, 0) AS sum_class
FROM exact_class ec
LEFT JOIN school s ON ec.classroom = s.classroom;

逻辑说明 #

• LEFT JOIN会保留exact_class中的所有班级记录，无论school表中是否存在对应数据
• COALESCE函数会返回传入参数中的第一个非空值：当school表无对应班级数据时，字段值为NULL，此时返回0；当有数据时，直接返回实际值，完美匹配需求

扩展版（同时显示已有班级和预设未录入班级） #

如果需要同时展示school表中已有的班级和预设的未录入班级，可以把已有班级也加入到exact_class中：

WITH exact_class AS (
 SELECT classroom FROM school
 UNION
 SELECT '2c' AS classroom FROM dual
 UNION
 SELECT '2d' AS classroom FROM dual
)
SELECT 
    ec.classroom,
    COALESCE(s.girls, 0) AS girls,
    COALESCE(s.boys, 0) AS boys,
    COALESCE(s.sum_class, 0) AS sum_class
FROM exact_class ec
LEFT JOIN school s ON ec.classroom = s.classroom;

关于IF子句的替代写法 #

如果一定要用IF逻辑，可使用CASE WHEN语句（兼容多数SQL方言），或者MySQL中的IFNULL函数，但写法不如左连接方案简洁：

CASE WHEN写法 #

WITH exact_class AS (
 SELECT '2c' AS classroom FROM dual
 UNION
 SELECT '2d' AS classroom FROM dual
)
SELECT 
    ec.classroom,
    CASE WHEN s.girls IS NULL THEN 0 ELSE s.girls END AS girls,
    CASE WHEN s.boys IS NULL THEN 0 ELSE s.boys END AS boys,
    CASE WHEN s.sum_class IS NULL THEN 0 ELSE s.sum_class END AS sum_class
FROM exact_class ec
LEFT JOIN school s ON ec.classroom = s.classroom;

MySQL IFNULL写法 #

WITH exact_class AS (
 SELECT '2c' AS classroom FROM dual
 UNION
 SELECT '2d' AS classroom FROM dual
)
SELECT 
    ec.classroom,
    IFNULL(s.girls, 0) AS girls,
    IFNULL(s.boys, 0) AS boys,
    IFNULL(s.sum_class, 0) AS sum_class
FROM exact_class ec
LEFT JOIN school s ON ec.classroom = s.classroom;

内容的提问来源于stack exchange，提问作者Orabeco