MySQL 在 true 时选择条目，在 false 时替换为 null

Question

我正在编制办公时间表。

我有一张 table users其中每个用户都有一个固定的时间表 hreg那是周一到周五。

我有一张 table hleave我在其中输入用户外出日期的休假代码。

表格hleave :

hleave_id   user        dateStart   dateEnd     leaveCode
int(11)PK   int(11)FK   date        date        int(11)FK
1           5           2019-02-11  2019-02-13  1
2           1           2019-02-28  2019-02-28  1
3           3           2019-02-26  2019-02-28  2

表格users :

user_id     firstName   lastName    link_team_id    link_hreg_id
int(11)PK   varchar     varchar     int(11)FK       int(11)FK
1           Bob         Smith       1               1
2           Alice       Fraser      1               1
3           Jenny       Summers     1               1
4           Carl        Raisman     1               1
5           Roger       Wayne       1               1

表格teams :

team_id     teamName
int(11)PK   varchar
1           team 1
2           team 2
3           team 3

表格hreg :

hreg_id     weekStart   weekEnd
int(11)PK   int(11)     int(11)
1           1           5
2           1           4
3           2           5

表格leave_codes :

code_id     codeName
int(11)PK   varchar
1           A
2           B 
3           C
4           D

如果有日期范围为本周开始、本周结束或本周之前开始并在本周之后结束的休假条目，我想显示休假代码。

如果没有，我想显示用户的常规时间表“工作”。例如:

        Monday  Tuesday Wednesday   Thursday    Friday
BOB     working working working     A           working
ALICE   working working working     working     working
JENNY   working B       B           B           working
CARL    working working working     working     working

这是我对 TEAM #1 的查询(为了清楚起见，我用实际日期替换了变量 $monday 和 $friday):

SELECT 
    users.firstName,
    users.lastName,
    users.link_team_id,
    users.link_hreg_id,
    hreg.weekStart,
    hreg.weekEnd,
    leave_codes.codeName,
    hleave.dateStart,
    hleave.dateEnd
FROM users
    LEFT JOIN hleave ON hleave.user = users.user_id
    JOIN teams ON users.link_team_id = teams.team_id 
    JOIN hreg ON users.link_hreg_id = hreg.hreg_id
    LEFT JOIN leave_codes ON hleave.leaveCode = leave_codes.code_id
WHERE
    (CASE 
        WHEN (hleave.dateStart BETWEEN '2019-02-25' AND '2019-03-01')
            THEN (users.link_team_id = 1)
        WHEN (hleave.dateEnd BETWEEN '2019-02-25' AND '2019-03-01')
            THEN (users.link_team_id = 1)
        WHEN (hleave.dateStart < '2019-02-25' AND hleave.dateEnd > '2019-03-01')
            THEN (users.link_team_id = 1)
        WHEN (hleave.dateStart IS NULL AND hleave.dateEnd IS NULL)
            THEN (users.link_team_id = 1)
    END)

结果:

firstName   lastName    link_team_id    link_hreg_id    weekStart   weekEnd     codeName    dateStart   dateEnd
Bob         Smith       1               1               1           5           A           2019-02-28  2019-02-28
Alice       Fraser      1               1               1           5           NULL        NULL        NULL
Jenny       Summers     1               1               1           5           B           2019-02-26  2019-02-28
Carl        Raisman     1               1               1           5           NULL        NULL        NULL

此查询有效。我得到 codeName , dateStart和 dateEnd如果这周发生，则为用户提供。如果dateStart和 dateEnd是 NULL，我仍然得到用户 firstName , lastName和 hreg所以我可以在网站上显示它们的工作。

到目前为止一切顺利，但我有一个问题。当dateStart < '2019-02-25'和 dateEnd < '2019-03-01' (即，如果休假发生在本周之前)由于日期范围验证为 FALSE 并且它也不是 NULL，因此我对该用户一无所获。例如，如果 Roger 在 2019-02-11 到 2019-02-13 休假，则 Roger 不包含在结果集中。

future 的日期范围也是如此:dateStart > '2019-02-25'和 dateEnd> '2019-03-01' .

实现此目标的最佳方法是什么？我在 SELECT 子句中尝试使用 CASE WHEN 来确定我想要选择的日期，但结果是一样的。我要么需要进行不同的选择，要么将验证为 FALSE 的条目变为 NULL？

Answer 1

这是解决您问题的方法。

首先，让我们将 5 个表连接在一起。这里的技巧是LEFT JOIN hleaves通过检查休假间隔是否与被过滤的周重叠来表；经典的方法是使用如下表达式:l.dateStart < @weekEnd AND l.dateEnd < @weekStart :

SELECT *
FROM 
    users u
    INNER JOIN teams t ON t.team_id = u.link_team_id
    INNER JOIN hreg  r ON r.hreg_id = u.link_hreg_id
    LEFT JOIN hleave l ON l.user = u.user_id AND l.dateStart < @weekEnd AND l.dateEnd < @weekStart
    LEFT JOIN leave_codes lc ON lc.code_id = l.leaveCode

有了这个数据集，我们现在可以使用条件聚合来产生所需的结果。例如，要在星期二检查状态，我们会这样做:

MAX(
    CASE 
        -- employee on leave
        WHEN DATE_ADD(@weekStart, INTERVAL 1 DAY) BETWEEN l.dateStart AND l.dateEnd THEN lc.codeName
        -- employee on schedule for that day
        WHEN 2 BETWEEN h.weekStart AND h.weekEnd THEN 'working'
        -- employee not on schedule for that day
        ELSE 'not working'
    END
) AS Tuesday

您可以为一周中的每一天重复此表达式，增加计数器。这是周一到周三的查询:

SELECT
    u.firstName,    
    MAX(
        CASE 
            WHEN @weekStart BETWEEN l.dateStart AND l.dateEnd THEN lc.codeName
            WHEN 2 BETWEEN h.weekStart AND h.weekEnd THEN 'working'
            ELSE 'not working'
        END
    ) AS Monday,
    MAX(
        CASE 
            WHEN DATE_ADD(@weekStart, INTERVAL 1 DAY) BETWEEN l.dateStart AND l.dateEnd THEN lc.codeName
            WHEN 2 BETWEEN h.weekStart AND h.weekEnd THEN 'working'
            ELSE 'not working'
        END
    ) AS Tuesday,
    MAX(
        CASE 
            WHEN DATE_ADD(@weekStart, INTERVAL 2 DAY) BETWEEN l.dateStart AND l.dateEnd THEN lc.codeName
            WHEN 3 BETWEEN h.weekStart AND h.weekEnd THEN 'working'
            ELSE 'not working'
        END
    ) AS Wednesday
    -- other days go here
FROM 
    users u
    INNER JOIN teams t ON t.team_id = u.link_team_id
    INNER JOIN hreg  r ON r.hreg_id = u.link_hreg_id
    LEFT JOIN hleave l ON l.user = u.user_id AND l.dateStart < @weekEnd AND l.dateEnd < @weekStart
    LEFT JOIN leave_codes lc ON lc.code_id = l.leaveCode
GROUP BY
    u.user_id,
    u.firstName