优化学生选课系统：使用SQL精确计算课程并发学生数

CREATE TABLE calendar_table (
    dt DATE PRIMARY KEY
);

-- 示例：填充2020年至2025年的日期
DELIMITER //
CREATE PROCEDURE FillCalendar(startDate DATE, endDate DATE)
BEGIN
    WHILE startDate <= endDate DO
        INSERT IGNORE INTO calendar_table (dt) VALUES (startDate);
        SET startDate = DATE_ADD(startDate, INTERVAL 1 DAY);
    END WHILE;
END //
DELIMITER ;

CALL FillCalendar('2020-01-01', '2025-12-31');

CREATE TABLE enrollments (
    IDStudent INT,
    StartDate DATE,
    EndDate DATE,
    IDCourse INT
);

-- 示例数据
INSERT INTO enrollments (IDStudent, StartDate, EndDate, IDCourse) VALUES
(1, '2021-01-01', '2021-01-02', 101),
(2, '2021-01-03', '2021-01-04', 101),
(3, '2020-12-20', '2021-02-01', 101),
(4, '2021-01-01', '2021-01-05', 102);

SELECT MAX(stcount) AS max_concurrent_students
FROM (
    SELECT c.dt, COUNT(e.IDStudent) AS stcount
    FROM calendar_table c
    JOIN enrollments e ON c.dt BETWEEN e.StartDate AND e.EndDate
    WHERE e.IDCourse = 101 -- 筛选特定课程
    GROUP BY c.dt
) AS countbydate
WHERE dt BETWEEN '2021-01-01' AND '2021-01-05';

<?php
// 假设你已经建立了数据库连接 $pdo

function getMaxConcurrentStudents(PDO $pdo, int $courseId, string $startDate, string $endDate): ?int
{
    $sql = "
        SELECT MAX(stcount) AS max_concurrent_students
        FROM (
            SELECT c.dt, COUNT(e.IDStudent) AS stcount
            FROM calendar_table c
            JOIN enrollments e ON c.dt BETWEEN e.StartDate AND e.EndDate
            WHERE e.IDCourse = :courseId
            GROUP BY c.dt
        ) AS countbydate
        WHERE dt BETWEEN :startDate AND :endDate;
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':courseId', $courseId, PDO::PARAM_INT);
    $stmt->bindParam(':startDate', $startDate, PDO::PARAM_STR);
    $stmt->bindParam(':endDate', $endDate, PDO::PARAM_STR);
    $stmt->execute();

    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result ? (int)$result['max_concurrent_students'] : null;
}

// 示例调用
try {
    $pdo = new PDO("mysql:host=localhost;dbname=your_db;charset=utf8", "username", "password");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $courseId = 101;
    $queryStartDate = '2021-01-01';
    $queryEndDate = '2021-01-05';

    $maxStudents = getMaxConcurrentStudents($pdo, $courseId, $queryStartDate, $queryEndDate);

    if ($maxStudents !== null) {
        echo "课程 {$courseId} 在 {$queryStartDate} 到 {$queryEndDate} 期间的最大并发学生数是: {$maxStudents} 人。\n";
    } else {
        echo "未找到相关数据或发生错误。\n";
    }

} catch (PDOException $e) {
    echo "数据库错误: " . $e->getMessage();
}
?>