利用SQL和日历表准确统计课程并发学生数

SELECT COUNT(*) FROM enrollments
WHERE IDCourse = ?
AND (
  (StartDate BETWEEN "<start date>" AND "<end date>")
  OR
  (EndDate BETWEEN "<start date>" AND "<end date>")
  OR
  (StartDate <= "<start date>" AND EndDate>= "<end date>")
);

-- 创建日历表
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');

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 = ? -- 筛选特定课程
GROUP BY
    c.dt;

SELECT
    MAX(stcount) AS MaxConcurrentStudents
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 = ? -- 替换为实际的课程ID
        GROUP BY
            c.dt
    ) AS countbydate
WHERE
    dt BETWEEN '2021-01-01' AND '2021-01-05'; -- 替换为您的目标日期范围

<?php
// 假设已建立PDO连接 $pdo
$courseId = 101;
$startDate = '2021-01-01';
$endDate = '2021-01-05';

$sql = "
    SELECT
        MAX(stcount) AS MaxConcurrentStudents
    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);
$maxConcurrentStudents = $result['MaxConcurrentStudents'];

echo "课程ID " . $courseId . " 在 " . $startDate . " 至 " . $endDate . " 期间的最大并发学生数为: " . $maxConcurrentStudents;
?>