使用SQL和PHP查找课程并发学生数：日历表方法

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

-- 创建日历表
CREATE TABLE calendar_table (
    dt DATE PRIMARY KEY
);

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

-- 调用存储过程填充数据
CALL FillCalendarTable('2020-01-01', '2025-12-31');

-- 或者，如果MySQL版本支持，可以使用递归CTE (MySQL 8+) 或更简洁的日期生成方式
-- 对于MySQL 5.6，上述存储过程是常见且可靠的方法。

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
    AND c.dt BETWEEN ? AND ? -- 替换为查询的开始日期和结束日期
GROUP BY
    c.dt
ORDER BY
    c.dt;

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 = ? -- 替换为目标课程ID
            AND c.dt BETWEEN ? AND ? -- 替换为查询的开始日期和结束日期
        GROUP BY
            c.dt
    ) AS daily_counts;

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
            AND c.dt BETWEEN '2021-01-01' AND '2021-01-05'
        GROUP BY
            c.dt
    ) AS daily_counts;

<?php

// 数据库连接配置
$host = 'localhost';
$db   = 'your_database_name';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 查询参数
$targetCourseId = 101;
$queryStartDate = '2021-01-01';
$queryEndDate   = '2021-01-05';

// SQL 查询语句
$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 = :course_id
                AND c.dt BETWEEN :start_date AND :end_date
            GROUP BY
                c.dt
        ) AS daily_counts;
";

try {
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':course_id', $targetCourseId, PDO::PARAM_INT);
    $stmt->bindParam(':start_date', $queryStartDate, PDO::PARAM_STR);
    $stmt->bindParam(':end_date', $queryEndDate, PDO::PARAM_STR);
    $stmt->execute();

    $result = $stmt->fetch();

    if ($result) {
        echo "课程ID " . $targetCourseId . " 在 " . $queryStartDate . " 到 " . $queryEndDate . " 期间的最大并发学生数是: " . $result['max_concurrent_students'];
    } else {
        echo "未找到数据。";
    }

} catch (\PDOException $e) {
    echo "查询失败: " . $e->getMessage();
}

?>