如何修改SQL语句解决旧版MySQL创建视图的子查询报错

针对旧版本MySQL不允许视图的SELECT语句FROM子句包含子查询的限制，提供两种可行的改写方案：

方案一：拆分中间视图（贴近原逻辑） #

先创建存储所有缺失年份明细的中间视图，再基于该视图聚合生成最终结果：

-- 第一步：创建中间视图，存储每个学生缺失的单一年份记录
CREATE VIEW vMissing_year_details AS
SELECT s.sid, s.first_name, s.last_name, 2014 AS missing_years
FROM Students s 
WHERE s.sid NOT IN (SELECT sid FROM Students_Courses WHERE year = 2014)
UNION ALL
SELECT s.sid, s.first_name, s.last_name, 2015
FROM Students s 
WHERE s.sid NOT IN (SELECT sid FROM Students_Courses WHERE year = 2015)
UNION ALL
SELECT s.sid, s.first_name, s.last_name, 2016
FROM Students s 
WHERE s.sid NOT IN (SELECT sid FROM Students_Courses WHERE year = 2016)
UNION ALL
SELECT s.sid, s.first_name, s.last_name, 2017
FROM Students s 
WHERE s.sid NOT IN (SELECT sid FROM Students_Courses WHERE year = 2017);

-- 第二步：基于中间视图生成最终聚合视图
CREATE VIEW vMissing_year_students AS
SELECT sid, 
       first_name, 
       last_name, 
       GROUP_CONCAT(missing_years ORDER BY missing_years) AS missing_years
FROM vMissing_year_details
GROUP BY sid, first_name, last_name;

这种方式完全保留原语句的逻辑结构，只是将原嵌套子查询拆分为独立的中间视图，避开了子查询嵌套的限制。

方案二：使用LEFT JOIN重构（无需额外视图） #

通过多次LEFT JOIN判断每个年份的选课情况，直接生成最终视图：

CREATE VIEW vMissing_year_students AS
SELECT 
    s.sid,
    s.first_name,
    s.last_name,
    GROUP_CONCAT(
        -- 标记缺失的年份
        CASE 
            WHEN sc2014.sid IS NULL THEN '2014'
            WHEN sc2015.sid IS NULL THEN '2015'
            WHEN sc2016.sid IS NULL THEN '2016'
            WHEN sc2017.sid IS NULL THEN '2017'
        END 
        -- 按年份排序拼接结果
        ORDER BY 
            CASE 
                WHEN sc2014.sid IS NULL THEN '2014'
                WHEN sc2015.sid IS NULL THEN '2015'
                WHEN sc2016.sid IS NULL THEN '2016'
                WHEN sc2017.sid IS NULL THEN '2017'
            END
    ) AS missing_years
FROM Students s
-- 关联各年份的选课记录
LEFT JOIN Students_Courses sc2014 ON s.sid = sc2014.sid AND sc2014.year = 2014
LEFT JOIN Students_Courses sc2015 ON s.sid = sc2015.sid AND sc2015.year = 2015
LEFT JOIN Students_Courses sc2016 ON s.sid = sc2016.sid AND sc2016.year = 2016
LEFT JOIN Students_Courses sc2017 ON s.sid = sc2017.sid AND sc2017.year = 2017
-- 过滤出至少有一个年份缺失的学生
WHERE sc2014.sid IS NULL OR sc2015.sid IS NULL OR sc2016.sid IS NULL OR sc2017.sid IS NULL
GROUP BY s.sid, s.first_name, s.last_name;

说明 #

• 每个LEFT JOIN对应一个年份的选课数据，若学生未选该年课程，关联结果会返回NULL
• CASE语句识别缺失的年份，GROUP_CONCAT将所有缺失年份按顺序拼接成字符串
• WHERE子句确保只返回有缺失年份的学生

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