mysql - 需要有关加入的帮助以及对 MySql 插入的一些计算

Question

我将尝试提供一些背景信息，以便您了解我在这里想要实现的目标。我的公司使用开源软件来管理员工休假(Jorani，随意谷歌 :))。

有不同类型的休假(假期、病假等)，我们想计算 2016 年假期“未使用”的天数，并将其“复制”到另一种称为“2016 年剩余假期”的休假类型.

重要的表是:

entitleddays(在这里您指定给员工的每种类型的天数)

id      employee    startdate   enddate     type    days    description
661     3           2016-01-01  2017-02-28  1       14.00   Holidays 2016
1296    3           2016-01-01  2016-12-31  4       18.00   Sick leave 2016

leaves(此表包含有关员工休假的信息)

id      startdate   enddate     status  employee    cause               duration    type
2436    2016-08-01  2016-08-01  3       78          OK from managers    1.00        1
2766    2016-09-05  2016-09-12  3       63          Holidays            6.00        1

所以基本上我们有:

授权的叶子:数据存储在上面显示的 ittleddays 表中。在我们的示例中，假设我的 2016 年假期有 14 天。

采叶:用户拍摄的叶子，存储在上面显示的名为 leaves 的表中。对于我们的示例，假设我在 8 月 1 日休假一天，9 月休假 6 天。

可用叶子:计算可用天数:授权天数减去“请假”。对于这个例子，14 天 - 7 = 7 天。所以我还有 7 天的假期 :D

因此，我的目标是为该用户插入这 7 天作为新类型的标题天数:“2016 年剩余天数”，并为每个用户执行此操作。所以我想到的解决方案是为每个用户做这样的事情:

INSERT INTO entitleddays (employee, startdate, enddate, type, days, description) 
    SELECT id, '2017-01-01', '2017-02-31', '8', (entitled holidays for 2016 minus all the taken leaves of this type), 'Remaining holidays from 2016'
    FROM users

其中 8 是我要复制天数的新型休假(2016 年剩余假期)。

例如，我可以为特定用户获取 2016 年的假期:

SELECT SUM(duration)
FROM leaves
WHERE employee=3 AND status=3 AND type=1

注意:类型 1 是休假类型“Holidays 2016”，状态 3 表示休假申请已被接受。

我可能可以在一个 SQL 指令中实现所有这些，但如果更简单或最容易管理/理解，它也可以拆分成更多。

非常感谢。

Answer 1

这是您可以处理计算的方式:

• 通过对表中每个员工的数据集进行分组，对子查询中的授权天数求和
  • 甚至可以按年分组？在这种情况下，我只是通过 WHERE-clause
  过滤了 2016 年
• 再次通过对每个员工进行分组，对子查询中的休假进行求和
  • 按年份分组或直接筛选您需要的年份
• 将此子查询连接到另一个查询的另一个结果集
• 在外部查询中计算(授权天数 - 休假)

查询:

SELECT
    entitled.employee,
    '2017-01-01',
    '2017-02-31',
    '8' AS type,
    entitled.days - takenDays.days,
    'Remaining holidays from 2016'
FROM
    (
        SELECT
            employee,
            SUM(days) AS days
        FROM
            entitleddays
        WHERE
            startdate >= '2016-01-01'
        AND type = 1
        GROUP BY
            employee
    ) AS entitled
LEFT JOIN (
    SELECT
        employee,
        SUM(duration) AS days
    FROM
        `leaves`
    WHERE
        startdate >= '2016-01-01'
    AND type = 1
    GROUP BY
        employee
) AS takenDays ON takenDays.employee = entitled.employee

我不确定这是否是您想要计算 ittleddays 和 taken days 总和的方式。该查询仅检查 startdate >= '2016-01-01'。

您在尝试中还提到了一个表 users 但没有提供该表的详细信息，所以我将其遗漏了。我想您可以将其用作其他基础。当前查询以entitleddays的分组结果为依据。

用于插入

INSERT INTO entitleddays (employee, startdate, enddate, type, days, description) 
    SELECT
        entitled.employee,
        '2017-01-01',
        '2017-02-31',
        '8' AS type,
        entitled.days - takenDays.days,
        'Remaining holidays from 2016'
    FROM
        (
            SELECT
                employee,
                SUM(days) AS days
            FROM
                entitleddays
            WHERE
                startdate >= '2016-01-01'
            AND type = 1
            GROUP BY
                employee
        ) AS entitled
    LEFT JOIN (
        SELECT
            employee,
            SUM(duration) AS days
        FROM
            `leaves`
        WHERE
            startdate >= '2016-01-01'
        AND type = 1
        GROUP BY
            employee
    ) AS takenDays ON takenDays.employee = entitled.employee