mysql - 将 WHERE 子句添加到 JOIN 语句

Question

我有 2 个表，employees 和 holidays。

在查看为今年输入的假期后，我正在尝试选择一个员工列表、他们允许的假期和剩余假期。

表格看起来像这样(只显示重要的行)

employees
    id,
    name,
    allowed_holiday

holiday
    id,
    employee,
    num_days,
    date_to

我的当前查询发现这些值很好，但我想添加一个 WHERE 子句，以便仅在 date_to > xxxx-09-01 AND < xxxx-09-01 时计算员工的假期。

    SELECT 
        e.id,
        e.name,
        e.allowed_holiday,
        (e.allowed_holiday - SUM(h.num_days)) AS remaining
    FROM
        employees AS e
    LEFT JOIN 
        holiday AS h
    ON
        h.employee = e.id
    GROUP BY
        e.name
    ORDER BY 
        e.name ASC

我不确定在哪里添加 WHERE 子句，但如果不从没有列出假期的数据中删除员工。

Answer 1

当您进行 LEFT 连接时，过滤条件必须在 ON 语句中。所以在你的情况下它看起来像这样:

SELECT 
    e.id,
    e.name,
    e.allowed_holiday,
    (e.allowed_holiday - SUM(h.num_days)) AS remaining
FROM
    employees AS e
LEFT JOIN 
    holiday AS h
ON
    h.employee = e.id AND h.to_date between xxxx-09-01 and xxxx-09-01
GROUP BY
    e.name
ORDER BY 
    e.name ASC