员工请假数据展示需求：整合Leave_Date与From/ToDate并优化SQL查询

要实现你的需求，核心是把多日请假的日期范围拆分成单个日期，再和单日请假的日期合并，之后再进行分组统计。下面分步骤给出修改方案：

1. 重构SQL查询（核心部分） #

我们需要用递归CTE（MySQL 8.0+支持）来生成多日请假的所有日期，再联合单日请假的数据，最后关联员工表统计。同时修复原代码的SQL注入风险（不要直接拼接变量到SQL）：

WITH leave_dates AS (
    -- 处理单日请假：直接取Leave_Date
    SELECT empid, Leave_Date AS leave_date
    FROM tblleaves
    WHERE Leave_Date IS NOT NULL
    AND YEAR(Leave_Date) = :year
    AND MONTH(Leave_Date) = :month

    UNION ALL

    -- 处理多日请假：递归生成fromDate到toDate的所有日期
    SELECT empid, DATE_ADD(fromDate, INTERVAL n DAY) AS leave_date
    FROM tblleaves
    JOIN (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
        UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
        UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
        UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
        UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
        UNION ALL SELECT 30
    ) AS numbers
    WHERE fromDate IS NOT NULL AND toDate IS NOT NULL
    AND YEAR(fromDate) <= :year AND YEAR(toDate) >= :year
    AND MONTH(fromDate) <= :month AND MONTH(toDate) >= :month
    AND DATE_ADD(fromDate, INTERVAL n DAY) <= toDate
    AND DATE_ADD(fromDate, INTERVAL n DAY) >= DATE_FORMAT(CONCAT(:year, '-', :month, '-01'), '%Y-%m-%d')
    AND DATE_ADD(fromDate, INTERVAL n DAY) <= LAST_DAY(CONCAT(:year, '-', :month, '-01'))
)
SELECT
    e.FirstName,
    e.LastName,
    COUNT(ld.leave_date) AS Leave_Days,
    GROUP_CONCAT(DISTINCT DATE_FORMAT(ld.leave_date, '%d-%m-%Y') SEPARATOR ', ') AS leave_dates
FROM leave_dates ld
JOIN tblemployees e ON ld.empid = e.id
GROUP BY e.id, e.FirstName, e.LastName
ORDER BY e.FirstName, e.LastName;

关键说明： #

• 递归CTE的numbers表生成0-30的数字，覆盖一个月内的所有可能天数，确保长周期请假的日期都能被拆分。
• 用DATE_FORMAT把日期转换成你需要的dd-mm-yyyy格式。
• 加入DISTINCT避免员工在同一日期既有单日请假又有多日请假的重复统计。
• 使用命名参数:year和:month彻底规避SQL注入风险。

2. 修改PHP代码适配新查询 #

把原PHP代码中的SQL替换成上面的语句，同时调整参数绑定逻辑：

<?php
if(isset($_POST['apply'])){
    $ym = $_POST['month'];
    list($Year, $Month) = explode("-", $ym, 2);
    
    // 重构后的SQL语句
    $sql = "WITH leave_dates AS (
        SELECT empid, Leave_Date AS leave_date
        FROM tblleaves
        WHERE Leave_Date IS NOT NULL
        AND YEAR(Leave_Date) = :year
        AND MONTH(Leave_Date) = :month

        UNION ALL

        SELECT empid, DATE_ADD(fromDate, INTERVAL n DAY) AS leave_date
        FROM tblleaves
        JOIN (
            SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
            UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
            UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
            UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
            UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
            UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
            UNION ALL SELECT 30
        ) AS numbers
        WHERE fromDate IS NOT NULL AND toDate IS NOT NULL
        AND YEAR(fromDate) <= :year AND YEAR(toDate) >= :year
        AND MONTH(fromDate) <= :month AND MONTH(toDate) >= :month
        AND DATE_ADD(fromDate, INTERVAL n DAY) <= toDate
        AND DATE_ADD(fromDate, INTERVAL n DAY) >= DATE_FORMAT(CONCAT(:year, '-', :month, '-01'), '%Y-%m-%d')
        AND DATE_ADD(fromDate, INTERVAL n DAY) <= LAST_DAY(CONCAT(:year, '-', :month, '-01'))
    )
    SELECT
        e.FirstName,
        e.LastName,
        COUNT(ld.leave_date) AS Leave_Days,
        GROUP_CONCAT(DISTINCT DATE_FORMAT(ld.leave_date, '%d-%m-%Y') SEPARATOR ', ') AS leave_dates
    FROM leave_dates ld
    JOIN tblemployees e ON ld.empid = e.id
    GROUP BY e.id, e.FirstName, e.LastName
    ORDER BY e.FirstName, e.LastName;";
    
    $query = $dbh->prepare($sql);
    // 绑定参数，避免SQL注入
    $query->bindParam(':year', $Year, PDO::PARAM_INT);
    $query->bindParam(':month', $Month, PDO::PARAM_INT);
    $query->execute();
    
    $results = $query->fetchAll(PDO::FETCH_OBJ);
    $cnt = 1;
    
    if($query->rowCount() > 0) {
        foreach($results as $result) {
?>
<tr>
    <td><?php echo htmlentities($cnt);?></td>
    <td><?php echo htmlentities($result->FirstName);?>&nbsp;<?php echo htmlentities($result->LastName);?></td>
    <td><?php echo htmlentities($result->Leave_Days); ?></td>
    <td><?php echo htmlentities($result->leave_dates); ?></td>
</tr>
<?php
            $cnt++;
        }
    }
}
?>

补充说明： #

• 分组时用e.id（员工唯一ID）作为核心分组依据，避免重名员工被错误合并。
• 保留了原代码的HTML输出结构，确保页面展示逻辑不变。

3. 兼容低版本MySQL（如果无法使用CTE） #

如果你的MySQL版本低于8.0，不支持递归CTE，可以预先创建一个包含所有可能日期的辅助表，或者用存储过程生成日期范围，但上面的CTE方案是最简洁高效的。

这样修改后，就能完美实现你需要的输出：包含单日请假日期、多日请假的所有日期，统计准确的请假天数，格式完全符合你的预期示例。

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