如何将含窗口函数的SQLite查询改写为无窗口函数版本？

可以通过关联子查询模拟序号的方式替代窗口函数，实现和原查询完全一致的结果，以下是两种适配不同场景的方案：

方案一（适配学生-账号一对多场景） #

如果你的实际业务中学生可能对应多个账号，用这个版本：

SELECT 
    s.StudentID,
    s.StudentName,
    a.AccountID,
    a.MonthAccountCreated,
    MAX(CASE WHEN idx = 1 THEN sch.SubjectName END) AS Subject1,
    MAX(CASE WHEN idx = 2 THEN sch.SubjectName END) AS Subject2
FROM 
    Students s
JOIN 
    Accounts a ON s.StudentID = a.FkStudentID
JOIN 
    Schedules sch ON a.AccountID = sch.FkAccountID
JOIN (
    -- 用关联子查询生成每个学生名下的科目序号，替代row_number
    SELECT 
        sch1.FkAccountID,
        sch1.SubjectName,
        (SELECT COUNT(*) 
         FROM Schedules sch2
         JOIN Accounts a2 ON sch2.FkAccountID = a2.AccountID
         WHERE a2.FkStudentID = (SELECT a3.FkStudentID FROM Accounts a3 WHERE a3.AccountID = sch1.FkAccountID)
         AND sch2.ScheduleID <= sch1.ScheduleID) AS idx
    FROM Schedules sch1
) AS ranked_sch ON sch.FkAccountID = ranked_sch.FkAccountID 
                AND sch.SubjectName = ranked_sch.SubjectName
WHERE 
    a.MonthAccountCreated = 'September'
GROUP BY 
    s.StudentID, s.StudentName, a.AccountID, a.MonthAccountCreated;

方案二（适配学生-账号一对一场景，更高效） #

如果你的业务中每个学生仅对应一个账号（和示例结构一致），可以简化子查询，提升执行效率：

SELECT 
    s.StudentID,
    s.StudentName,
    a.AccountID,
    a.MonthAccountCreated,
    MAX(CASE WHEN idx = 1 THEN sch.SubjectName END) AS Subject1,
    MAX(CASE WHEN idx = 2 THEN sch.SubjectName END) AS Subject2
FROM 
    Students s
JOIN 
    Accounts a ON s.StudentID = a.FkStudentID
JOIN 
    Schedules sch ON a.AccountID = sch.FkAccountID
JOIN (
    -- 直接按账号统计科目序号
    SELECT 
        sch1.FkAccountID,
        sch1.SubjectName,
        (SELECT COUNT(*) 
         FROM Schedules sch2
         WHERE sch2.FkAccountID = sch1.FkAccountID
         AND sch2.ScheduleID <= sch1.ScheduleID) AS idx
    FROM Schedules sch1
) AS ranked_sch ON sch.FkAccountID = ranked_sch.FkAccountID 
                AND sch.SubjectName = ranked_sch.SubjectName
WHERE 
    a.MonthAccountCreated = 'September'
GROUP BY 
    s.StudentID, s.StudentName, a.AccountID, a.MonthAccountCreated;

核心逻辑说明 #

两个方案的本质都是用COUNT(*)关联子查询模拟row_number()的序号生成：

1. 对每条Schedule记录，统计同一个学生（或账号）下，ScheduleID小于等于当前记录的数量，以此作为科目序号idx
2. 后续通过MAX(CASE...)将序号对应的科目转为横向列，最后按学生分组聚合，逻辑和原查询完全一致

如果你的Schedule表有更合适的排序字段（比如科目创建时间），可以把sch2.ScheduleID <= sch1.ScheduleID替换成对应字段的比较，确保序号顺序符合业务需求。

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