请假申请月度报表开发：多表关联数据展示优化需求

我明白你现在的需求：要把员工的单日请假和多日请假的所有日期整合起来，同时展示员工姓名、总请假天数和完整的请假日期列表。原有的PHP查询应该是没处理好多日请假的日期展开，下面我给你一套完整的解决方案：

第一步：编写正确的SQL查询（以MySQL为例） #

首先需要用递归CTE把多日请假的fromDate到toDate之间的所有日期都展开，再和单日请假的Leave_Date合并，最后关联员工表统计数据：

WITH leave_dates AS (
    -- 处理单日请假
    SELECT 
        e.`employee id` AS emp_id,
        e.`employee name` AS emp_name,
        l.Leave_Date AS leave_date,
        'single' AS leave_type
    FROM tblemployee e
    JOIN tblleaves l ON e.`employee id` = l.empid
    WHERE l.Leave_Date IS NOT NULL
    UNION ALL
    -- 处理多日请假，递归生成日期序列
    SELECT 
        e.`employee id` AS emp_id,
        e.`employee name` AS emp_name,
        DATE_ADD(l.fromDate, INTERVAL seq.seq DAY) AS leave_date,
        'range' AS leave_type
    FROM tblemployee e
    JOIN tblleaves l ON e.`employee id` = l.empid
    JOIN (
        SELECT 0 AS seq 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
        -- 若需支持更长请假时长，继续添加数字即可
    ) seq ON DATE_ADD(l.fromDate, INTERVAL seq.seq DAY) <= l.toDate
    WHERE l.fromDate IS NOT NULL AND l.toDate IS NOT NULL
)
SELECT 
    emp_name,
    COUNT(DISTINCT leave_date) AS leave_days,
    -- 拼接日期并添加多日标记
    CASE 
        WHEN MAX(leave_type) = 'range' AND MIN(leave_type) = 'range' 
        THEN CONCAT(GROUP_CONCAT(DISTINCT DATE_FORMAT(leave_date, '%d-%m-%Y') ORDER BY leave_date SEPARATOR ', '), ' (FromDate and ToDate)')
        WHEN MAX(leave_type) = 'single' AND MIN(leave_type) = 'single'
        THEN GROUP_CONCAT(DISTINCT DATE_FORMAT(leave_date, '%d-%m-%Y') ORDER BY leave_date SEPARATOR ', ')
        ELSE CONCAT(
            GROUP_CONCAT(DISTINCT CASE WHEN leave_type='single' THEN DATE_FORMAT(leave_date, '%d-%m-%Y') END ORDER BY leave_date SEPARATOR ', '),
            ', ',
            GROUP_CONCAT(DISTINCT CASE WHEN leave_type='range' THEN DATE_FORMAT(leave_date, '%d-%m-%Y') END ORDER BY leave_date SEPARATOR ', '),
            ' (FromDate and ToDate)'
        )
    END AS leave_dates
FROM leave_dates
GROUP BY emp_id, emp_name
ORDER BY emp_name;

说明：

• 递归CTE里的seq表可以根据实际最长请假天数扩展数字范围
• 通过leave_type标记区分单日/多日请假，最终拼接时匹配你示例中的格式要求
• DATE_FORMAT负责将日期转换为dd-mm-yyyy格式

第二步：PHP代码处理与展示 #

接下来用PHP执行查询并在页面展示结果：

<?php
// 初始化数据库连接（根据你的实际配置修改）
$conn = new mysqli('localhost', 'your_username', 'your_password', 'your_database');
if ($conn->connect_error) {
    die("数据库连接失败: " . $conn->connect_error);
}

// 执行上述SQL查询
$sql = "WITH leave_dates AS (
    SELECT 
        e.`employee id` AS emp_id,
        e.`employee name` AS emp_name,
        l.Leave_Date AS leave_date,
        'single' AS leave_type
    FROM tblemployee e
    JOIN tblleaves l ON e.`employee id` = l.empid
    WHERE l.Leave_Date IS NOT NULL
    UNION ALL
    SELECT 
        e.`employee id` AS emp_id,
        e.`employee name` AS emp_name,
        DATE_ADD(l.fromDate, INTERVAL seq.seq DAY) AS leave_date,
        'range' AS leave_type
    FROM tblemployee e
    JOIN tblleaves l ON e.`employee id` = l.empid
    JOIN (
        SELECT 0 AS seq 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
    ) seq ON DATE_ADD(l.fromDate, INTERVAL seq.seq DAY) <= l.toDate
    WHERE l.fromDate IS NOT NULL AND l.toDate IS NOT NULL
)
SELECT 
    emp_name,
    COUNT(DISTINCT leave_date) AS leave_days,
    CASE 
        WHEN MAX(leave_type) = 'range' AND MIN(leave_type) = 'range' 
        THEN CONCAT(GROUP_CONCAT(DISTINCT DATE_FORMAT(leave_date, '%d-%m-%Y') ORDER BY leave_date SEPARATOR ', '), ' (FromDate and ToDate)')
        WHEN MAX(leave_type) = 'single' AND MIN(leave_type) = 'single'
        THEN GROUP_CONCAT(DISTINCT DATE_FORMAT(leave_date, '%d-%m-%Y') ORDER BY leave_date SEPARATOR ', ')
        ELSE CONCAT(
            GROUP_CONCAT(DISTINCT CASE WHEN leave_type='single' THEN DATE_FORMAT(leave_date, '%d-%m-%Y') END ORDER BY leave_date SEPARATOR ', '),
            ', ',
            GROUP_CONCAT(DISTINCT CASE WHEN leave_type='range' THEN DATE_FORMAT(leave_date, '%d-%m-%Y') END ORDER BY leave_date SEPARATOR ', '),
            ' (FromDate and ToDate)'
        )
    END AS leave_dates
FROM leave_dates
GROUP BY emp_id, emp_name
ORDER BY emp_name;";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出表格样式的结果
    echo "<table style='border-collapse: collapse; width: 80%; margin: 20px auto;'>
            <tr>
                <th style='border: 1px solid #ddd; padding: 8px; background-color: #f2f2f2;'>Employee Name</th>
                <th style='border: 1px solid #ddd; padding: 8px; background-color: #f2f2f2;'>Leave Days</th>
                <th style='border: 1px solid #ddd; padding: 8px; background-color: #f2f2f2;'>Leave Dates</th>
            </tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr>
                <td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($row["emp_name"]) . "</td>
                <td style='border: 1px solid #ddd; padding: 8px; text-align: center;'>" . $row["leave_days"] . "</td>
                <td style='border: 1px solid #ddd; padding: 8px;'>" . htmlspecialchars($row["leave_dates"]) . "</td>
              </tr>";
    }
    echo "</table>";
} else {
    echo "<p style='text-align: center;'>暂无请假记录</p>";
}
$conn->close();
?>

额外适配说明 #

如果你的数据库不是MySQL（比如PostgreSQL），递归CTE的语法会略有差异，但核心思路都是展开多日请假的日期范围后再合并统计。若需要调整日期标记的显示逻辑，也可以直接修改SQL中的CASE分支或PHP的输出代码。

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