为何目标SQL查询返回空结果？如何优化以在空表场景下返回课程信息？

你的原查询出现空结果的核心原因是使用了内连接（逗号连接子查询等价于INNER JOIN），当Enrolls表为空时，course_student子查询没有返回数据，导致主表Course的行被过滤掉了。同时，折扣的关联逻辑也可以优化，确保只关联当前有效的折扣。

下面是优化后的查询，能满足你的所有需求：

基础优化版本（单折扣场景） #

如果每个课程最多只有一个当前有效的折扣，用这个版本即可：

SELECT 
    C.course_id,
    C.course_name,
    C.average_rating,
    COALESCE(es.total_student, 0) AS total_student,
    COALESCE(D.percentage, 0) AS percentage,
    CASE 
        WHEN D.discount_id IS NOT NULL THEN 
            C.course_price * ((100 - D.percentage) / 100)
        ELSE 
            C.course_price 
    END AS price
FROM Course C
-- 左连接选课统计，确保无选课的课程显示0
LEFT JOIN (
    SELECT course_id, COUNT(student_id) AS total_student
    FROM Enrolls
    GROUP BY course_id
) es ON C.course_id = es.course_id
-- 左连接当前有效的折扣，只关联符合条件的折扣记录
LEFT JOIN Discount D 
    ON C.course_id = D.discounted_course_id
    AND CURRENT_DATE BETWEEN D.start_date AND D.end_date
    AND D.is_allowed
-- 筛选目标创建者的课程
WHERE C.course_creator_id = '$person_id'

多折扣场景处理（可选） #

如果一个课程可能存在多个当前有效的折扣，我们可以通过窗口函数选择最优的折扣（比如最高折扣比例），避免返回重复行：

SELECT 
    C.course_id,
    C.course_name,
    C.average_rating,
    COALESCE(es.total_student, 0) AS total_student,
    COALESCE(dc.percentage, 0) AS percentage,
    CASE 
        WHEN dc.percentage IS NOT NULL THEN 
            C.course_price * ((100 - dc.percentage) / 100)
        ELSE 
            C.course_price 
    END AS price
FROM Course C
LEFT JOIN (
    SELECT course_id, COUNT(student_id) AS total_student
    FROM Enrolls
    GROUP BY course_id
) es ON C.course_id = es.course_id
-- 子查询获取每个课程的最优有效折扣
LEFT JOIN (
    SELECT 
        discounted_course_id,
        percentage,
        -- 按折扣比例从高到低排序，取第一个
        ROW_NUMBER() OVER (PARTITION BY discounted_course_id ORDER BY percentage DESC) AS rn
    FROM Discount
    WHERE CURRENT_DATE BETWEEN start_date AND end_date
      AND is_allowed
) dc ON C.course_id = dc.discounted_course_id AND dc.rn = 1
WHERE C.course_creator_id = '$person_id'

关键优化点说明： #

1. 从主表Course出发：确保所有course_creator_id为$person_id的课程都会被返回，不受其他表空数据的影响。
2. 左连接选课统计：用LEFT JOIN替代原查询的内连接，配合COALESCE将无选课的total_student转为0（如果不需要转0，去掉COALESCE即可保留NULL）。
3. 折扣关联条件移到ON子句：只关联当前处于有效期且允许的折扣，没有符合条件的折扣时，percentage会是NULL（或通过COALESCE转为0）。
4. 多折扣处理：通过窗口函数ROW_NUMBER()给每个课程的有效折扣排序，避免同一课程返回多行结果。

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