如何用SQL查询获取用户未来有排课的前7天课程信息？

数据库表结构 #

核心需求 #

获取指定用户（按student_id或instructor_id筛选）未来有排课的前7个日期的所有课程：

• 跳过无排课的日期，持续往后查找直到凑够7个有课的日期
• 单个日期内的多节课程需全部返回

遇到的问题 #

使用Spring JPA内置方法及简单GROUP BY、LIMIT的自定义查询均无法满足需求，无法正确跳过无课日期并收集够7个有效日期的课程。

1. PostgreSQL 查询语句实现 #

利用PostgreSQL的窗口函数DENSE_RANK()对有课日期排序，筛选前7个日期后关联原表获取所有对应课程。

学生用户查询（按student_id筛选） #

WITH ranked_dates AS (
    SELECT
        DISTINCT DATE(lesson_start) AS lesson_date,
        DENSE_RANK() OVER (ORDER BY DATE(lesson_start)) AS date_rank
    FROM
        your_table_name
    WHERE
        student_id = :studentId
        AND lesson_start >= CURRENT_TIMESTAMP
    ORDER BY
        lesson_date
)
SELECT
    t.*
FROM
    your_table_name t
JOIN
    ranked_dates rd ON DATE(t.lesson_start) = rd.lesson_date
WHERE
    rd.date_rank <= 7
    AND t.student_id = :studentId
    AND t.lesson_start >= CURRENT_TIMESTAMP
ORDER BY
    t.lesson_start;

讲师用户查询（按instructor_id筛选） #

将上述SQL中的student_id替换为instructor_id即可。

逻辑说明 #

• WITH ranked_dates子句：筛选指定用户未来所有有课日期，去重后按日期顺序排名，无课日期不会被纳入排名
• 主查询：关联原表，仅取排名前7的日期对应的所有课程，确保返回凑够7个有课日期的全部课程

2. Spring JPA 集成实现 #

在Repository接口中使用@Query引入上述SQL，替换实际表名：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import java.util.List;

public interface LessonRepository extends CrudRepository<LessonEntity, Long> {

    @Query(value = """
        WITH ranked_dates AS (
            SELECT
                DISTINCT DATE(lesson_start) AS lesson_date,
                DENSE_RANK() OVER (ORDER BY DATE(lesson_start)) AS date_rank
            FROM
                lesson_table
            WHERE
                student_id = :studentId
                AND lesson_start >= CURRENT_TIMESTAMP
            ORDER BY
                lesson_date
        )
        SELECT
            t.*
        FROM
            lesson_table t
        JOIN
            ranked_dates rd ON DATE(t.lesson_start) = rd.lesson_date
        WHERE
            rd.date_rank <= 7
            AND t.student_id = :studentId
            AND t.lesson_start >= CURRENT_TIMESTAMP
        ORDER BY
            t.lesson_start
    """, nativeQuery = true)
    List<LessonEntity> findNext7DaysLessonsForStudent(Long studentId);

    // 讲师版本
    @Query(value = """
        WITH ranked_dates AS (
            SELECT
                DISTINCT DATE(lesson_start) AS lesson_date,
                DENSE_RANK() OVER (ORDER BY DATE(lesson_start)) AS date_rank
            FROM
                lesson_table
            WHERE
                instructor_id = :instructorId
                AND lesson_start >= CURRENT_TIMESTAMP
            ORDER BY
                lesson_date
        )
        SELECT
            t.*
        FROM
            lesson_table t
        JOIN
            ranked_dates rd ON DATE(t.lesson_start) = rd.lesson_date
        WHERE
            rd.date_rank <= 7
            AND t.instructor_id = :instructorId
            AND t.lesson_start >= CURRENT_TIMESTAMP
        ORDER BY
            t.lesson_start
    """, nativeQuery = true)
    List<LessonEntity> findNext7DaysLessonsForInstructor(Long instructorId);
}

注意事项 #

• 替换lesson_table为实际数据库表名
• LessonEntity为对应表的JPA实体类，需确保字段映射正确
• 若使用MySQL 8.0+，语法基本通用，仅需确认日期函数和窗口函数支持

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