PHP项目中基于日期区间的SQL人员可用性查询方案

针对你的需求，我们可以通过CTE（公共表表达式）分步处理占用时段、合并重叠区间，最终计算出参考日期内的人员可用时段。以下是适配你数据表的SQL查询：

WITH person_occupied AS (
    -- 整理每个人员的有效占用时段（修正日期顺序错误）
    SELECT 
        p.p_id,
        LEAST(f.f_from, f.f_to) AS occupied_start,
        GREATEST(f.f_from, f.f_to) AS occupied_end
    FROM person p
    JOIN affect a ON p.p_id = a.fk_p_id
    JOIN field f ON a.fk_f_id = f.f_id
),
merged_occupied AS (
    -- 合并每个人员的重叠/连续占用时段
    SELECT 
        p_id,
        occupied_start,
        MAX(occupied_end) AS occupied_end
    FROM (
        SELECT 
            p_id,
            occupied_start,
            occupied_end,
            SUM(CASE WHEN occupied_start <= LAG(occupied_end) OVER (PARTITION BY p_id ORDER BY occupied_start) THEN 0 ELSE 1 END) OVER (PARTITION BY p_id ORDER BY occupied_start) AS group_id
        FROM person_occupied
    ) t
    GROUP BY p_id, group_id, occupied_start
),
available_intervals AS (
    -- 计算参考区间内的可用时段边界
    SELECT 
        p.p_id,
        CASE 
            WHEN prev_end IS NULL THEN '2022-04-25'
            ELSE prev_end
        END AS available_start,
        CASE 
            WHEN curr_start IS NULL THEN '2022-07-08'
            ELSE curr_start
        END AS available_end
    FROM (
        -- 生成占用时段的前后边界 + 参考区间边界
        SELECT 
            p_id,
            NULL AS prev_end,
            occupied_start AS curr_start
        FROM merged_occupied
        UNION ALL
        SELECT 
            p_id,
            occupied_end AS prev_end,
            NULL AS curr_start
        FROM merged_occupied
        UNION ALL
        SELECT 
            p_id,
            '2022-04-25' AS prev_end,
            '2022-07-08' AS curr_start
        FROM person
    ) t
    JOIN person p ON t.p_id = p.p_id
    -- 过滤出有效可用区间（与参考区间有交集且开始<结束）
    WHERE (prev_end IS NULL OR prev_end <= '2022-07-08')
      AND (curr_start IS NULL OR curr_start >= '2022-04-25')
      AND (prev_end < curr_start OR (prev_end IS NULL AND curr_start > '2022-04-25') OR (curr_start IS NULL AND prev_end < '2022-07-08'))
),
final_available AS (
    -- 合并同一人员的连续可用区间
    SELECT 
        p_id,
        MIN(available_start) AS available_from,
        MAX(available_end) AS available_to
    FROM available_intervals
    GROUP BY p_id
)
-- 格式化输出，替换参考区间边界为'-'
SELECT 
    p_id,
    CASE WHEN available_from = '2022-04-25' THEN '-' ELSE available_from END AS available_from,
    CASE WHEN available_to = '2022-07-08' THEN '-' ELSE available_to END AS available_to
FROM final_available
-- 过滤无可用时段的人员
WHERE available_from < available_to
ORDER BY p_id;

关键逻辑说明 #

1. person_occupied：修正field表中日期顺序错误的时段（如f_from > f_to的情况），确保每个占用时段的开始时间早于结束时间。
2. merged_occupied：使用窗口函数合并同一人员的重叠或连续占用时段，避免重复计算。
3. available_intervals：生成所有可能的可用区间边界，筛选出落在参考范围内的有效可用区间。
4. final_available：合并同一人员的连续可用区间，得到最终的可用时段范围。
5. 最后一步格式化输出，将等于参考区间起始/结束的时间替换为-，并过滤掉无可用时段的人员。

PHP中安全使用示例 #

为避免SQL注入，建议使用PDO参数绑定：

$from = '2022-04-25';
$to = '2022-07-08';

// 初始化PDO连接
$pdo = new PDO('mysql:host=你的数据库地址;dbname=你的数据库名', '用户名', '密码');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 准备SQL语句
$sql = <<<SQL
WITH person_occupied AS (
    SELECT 
        p.p_id,
        LEAST(f.f_from, f.f_to) AS occupied_start,
        GREATEST(f.f_from, f.f_to) AS occupied_end
    FROM person p
    JOIN affect a ON p.p_id = a.fk_p_id
    JOIN field f ON a.fk_f_id = f.f_id
),
merged_occupied AS (
    SELECT 
        p_id,
        occupied_start,
        MAX(occupied_end) AS occupied_end
    FROM (
        SELECT 
            p_id,
            occupied_start,
            occupied_end,
            SUM(CASE WHEN occupied_start <= LAG(occupied_end) OVER (PARTITION BY p_id ORDER BY occupied_start) THEN 0 ELSE 1 END) OVER (PARTITION BY p_id ORDER BY occupied_start) AS group_id
        FROM person_occupied
    ) t
    GROUP BY p_id, group_id, occupied_start
),
available_intervals AS (
    SELECT 
        p.p_id,
        CASE 
            WHEN prev_end IS NULL THEN :from
            ELSE prev_end
        END AS available_start,
        CASE 
            WHEN curr_start IS NULL THEN :to
            ELSE curr_start
        END AS available_end
    FROM (
        SELECT 
            p_id,
            NULL AS prev_end,
            occupied_start AS curr_start
        FROM merged_occupied
        UNION ALL
        SELECT 
            p_id,
            occupied_end AS prev_end,
            NULL AS curr_start
        FROM merged_occupied
        UNION ALL
        SELECT 
            p_id,
            :from AS prev_end,
            :to AS curr_start
        FROM person
    ) t
    JOIN person p ON t.p_id = p.p_id
    WHERE (prev_end IS NULL OR prev_end <= :to)
      AND (curr_start IS NULL OR curr_start >= :from)
      AND (prev_end < curr_start OR (prev_end IS NULL AND curr_start > :from) OR (curr_start IS NULL AND prev_end < :to))
),
final_available AS (
    SELECT 
        p_id,
        MIN(available_start) AS available_from,
        MAX(available_end) AS available_to
    FROM available_intervals
    GROUP BY p_id
)
SELECT 
    p_id,
    CASE WHEN available_from = :from THEN '-' ELSE available_from END AS available_from,
    CASE WHEN available_to = :to THEN '-' ELSE available_to END AS available_to
FROM final_available
WHERE available_from < available_to
ORDER BY p_id;
SQL;

// 执行查询
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':from', $from);
$stmt->bindParam(':to', $to);
$stmt->execute();

// 获取结果
$availability = $stmt->fetchAll(PDO::FETCH_ASSOC);

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