2019

2019

关注
发信
关注(28)粉丝(399)

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

我正在制定办公室时间表。

我有一个表users，其中每个用户都有一个常规时间表hreg，该时间表是星期一至星期五。

我有一个表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不包括在结果集中。

将来的日期范围也一样：dateStart > '2019-02-25'和dateEnd> '2019-03-01'。

实现这一目标的最佳方法是什么？我在SELECT子句中尝试了CASE WHEN来确定我想要选择的日期，但结果是相同的。我需要选择其他方法还是将验证为FALSE的条目转换为NULL？

最佳答案

正如我在评论中指出的那样，您似乎使查询变得比所需的更为复杂。

主要问题是您的CASE/WHEN标准中的WHERE，将users限制为与CASE/WHEN中的匹配，由于罗杰休假，但与WHERE标准中的指定日期不匹配，被排除在外。

由于您似乎只对特定团队感兴趣，因此可以直接在主查询中过滤用户团队。假设hleave仅在user休假查询日期范围内时包含一个条目，则条件可以从WHERE移至LEFT JOIN，并在用户不休假时显示NULL。

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
JOIN teams
ON users.link_team_id = teams.team_id
JOIN hreg
ON users.link_hreg_id = hreg.hreg_id
LEFT JOIN hleave
ON hleave.user = users.user_id
AND (
    hleave.dateStart BETWEEN '2019-02-25' AND '2019-03-01' #leave starts this week
    OR
    hleave.dateEnd BETWEEN '2019-02-25' AND '2019-03-01' #leave ends this week
    OR
    (hleave.dateStart < '2019-02-25' AND hleave.dateEnd > '2019-03-01') #leave started before this week and continues after
)
LEFT JOIN leave_codes
ON hleave.leaveCode = leave_codes.code_id
WHERE users.link_team_id = 1


结果是：



    <table>
      <thead>
        <tr>
          <th class="col0">firstName</th>
          <th class="col1">lastName</th>
          <th class="col2">link_team_id</th>
          <th class="col3">link_hreg_id</th>
          <th class="col4">weekStart</th>
          <th class="col5">weekEnd</th>
          <th class="col6">codeName</th>
          <th class="col7">dateStart</th>
          <th class="col8">dateEnd</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td class="col0">Bob</td>
          <td class="col1">Smith</td>
          <td class="col2">1</td>
          <td class="col3">1</td>
          <td class="col4">1</td>
          <td class="col5">5</td>
          <td class="col6">A</td>
          <td class="col7">2019-02-28</td>
          <td class="col8">2019-02-28</td>
        </tr>
        <tr>
          <td class="col0">Jenny</td>
          <td class="col1">Summers</td>
          <td class="col2">1</td>
          <td class="col3">1</td>
          <td class="col4">1</td>
          <td class="col5">5</td>
          <td class="col6">B</td>
          <td class="col7">2019-02-26</td>
          <td class="col8">2019-02-28</td>
        </tr>
        <tr>
          <td class="col0">Alice</td>
          <td class="col1">Fraser</td>
          <td class="col2">1</td>
          <td class="col3">1</td>
          <td class="col4">1</td>
          <td class="col5">5</td>
          <td class="col6"></td>
          <td class="col7"></td>
          <td class="col8"></td>
        </tr>
        <tr>
          <td class="col0">Carl</td>
          <td class="col1">Raisman</td>
          <td class="col2">1</td>
          <td class="col3">1</td>
          <td class="col4">1</td>
          <td class="col5">5</td>
          <td class="col6"></td>
          <td class="col7"></td>
          <td class="col8"></td>
        </tr>
        <tr>
          <td class="col0">Roger</td>
          <td class="col1">Wayne</td>
          <td class="col2">1</td>
          <td class="col3">1</td>
          <td class="col4">1</td>
          <td class="col5">5</td>
          <td class="col6"></td>
          <td class="col7"></td>
          <td class="col8"></td>
        </tr>
      </tbody>
    </table>

关于mysql - MySQL在true时选择条目，在false时替换为null，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54974807/

10-14 13:04
Powered by LMLPHP ©2024 2019 0.029750