在 MySQL 中使用三个表进行计算

我试图计算每项任务的总成本.有一个任务分配表，其中包含分配给特定员工的任务，以及开始到完成日期&amp；时间另一方面，还有另一个表格，其中包含员工的小时工资率.对于一项任务，成本将是他们工作的小时数乘以他们的赌注率.此外，我还有另一个表格，其中也有各种成本/支出，必须添加到总和中，以获得任务的最终成本.

因此，我的方法是将start&amp；完成时间到秒使用到秒，然后从开始减go 完成时间，计算员工工作的总小时数.然后我试着提取员工的赌注率，然后乘以它.然而，我的输出不准确，有时甚至不接近.

我的看法似乎合乎逻辑.然而，我的质疑或理解是不对的.首先，我要展示我的桌子 struct .

Task Table:在该表中，我存储了各种任务细节，如标题、位置、开始日期等.

Task Assignment:此表包含有关分配给哪个员工的任务的信息，以及该员工工作的开始和完成时间.

Employee wagers related table:本表列出了每位员工的小时工资率.

Additional Cost:在这个表中，我使用连接键task_id使它们各自属于任务表中的一个任务.

现在我将向你们展示我写的问题.

Approach 3 from the first answer:

WITH task_hours AS (
    SELECT
        ta.task_id,
        ta.employee_id,
        (TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600 AS hours_worked
    FROM task_assignment AS ta
)

SELECT
    t.title,
    SUM(ac.cost) as addiCost,
    SUM(th.hours_worked) AS totalHrs,
    SUM(th.hours_worked * wrd.wages_rate) AS cost
FROM task t
        JOIN task_hours AS th ON t.id = th.task_id
        JOIN work_rights_detail wrd on th.employee_id = wrd.account_id
        JOIN additional_cost ac on t.id = ac.task_id
GROUP BY t.id

Approach 1:

    SELECT
           # we need the title of the task
           t.title,
           
           # total hours completed on this task by employees
           # dividing the diff by 3600 to get sec into hour form
           SUM(((TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600)) totalHrs,
    
           # calculate the total cost by each employee and sum it up
           SUM(((TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600)) * wrd.wages_rate cost
    FROM task t
    JOIN task_assignment ta on t.id = ta.task_id
    JOIN work_rights_detail wrd on ta.employee_id = wrd.account_id
    GROUP BY ta.task_id

Approach 2

SELECT
       # we need the title of the task
       t.title,

       # total hours completed on this task by employees
       # dividing the diff by 3600 to get sec into hour form
       SUM(((TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600)) totalHrs,

       # calculate the total cost by each employee and sum it up
       SUM(((TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600))
           *
       (SELECT wrd.wages_rate FROM work_rights_detail wrd WHERE wrd.account_id = ta.employee_id) cost
FROM task t
JOIN task_assignment ta on t.id = ta.task_id
GROUP BY ta.task_id

Output lastest:对于我的最新查询，我有以下输出，其中我应该有Output lastest:作为任务1的额外成本.而工作时间和任务成本又是不确定的.

Output old:任务1的输出不正确.它应该是(10x1)+(11.5x1)=21.5，然而，我得到了20！

Can you please tell me what exactly I'm doing wrong here. More specifically, can you please describe why I'm getting this seemingly correct but incorrect result?

下面是表格 struct 和数据.

create table task
(
    id               int auto_increment
        primary key,
    title            varchar(255)     not null,
    note             text             not null,
    location         varchar(255)     null,
    email            varchar(255)     null,
    status           int(1) default 0 not null,
    commence         varchar(255)     not null,
    deadline         varchar(255)     not null,
    client_name      varchar(255)     not null,
    phone            varchar(15)      null,
    address          varchar(255)     null,
    estimated_budget float            not null,
    upfront_payment  float            not null,
    expense          float  default 0 not null,
    created          varchar(255)     not null
)
    charset = latin1;

INSERT INTO camshine.task (id, title, note, location, email, status, commence, deadline, client_name, phone, address, estimated_budget, upfront_payment, expense, created) VALUES (1, 'Task 1', 'This is note.', 'Haverhill', 'root@data21.com', 0, '2022-04-16T12:00:00+0100', '2022-04-18T12:00:00+0100', 'Rootdata21', '01747520068', 'this is address.', 1000, 150, 0, '2022-04-15T17:07:56+0100');
INSERT INTO camshine.task (id, title, note, location, email, status, commence, deadline, client_name, phone, address, estimated_budget, upfront_payment, expense, created) VALUES (2, 'Task 2', 'This is note.', 'Haverhill', 'root@data21.com', 0, '2022-04-16T12:00:00+0100', '2022-04-18T12:00:00+0100', 'Rootdata21', '01747520068', 'this is address.', 1000, 150, 0, '2022-04-15T17:07:56+0100');


create table task_assignment
(
    id          int auto_increment
        primary key,
    task_id     int          not null,
    employee_id int          not null,
    start       varchar(255) not null,
    finish      varchar(255) not null
)
    charset = latin1;

INSERT INTO camshine.task_assignment (id, task_id, employee_id, start, finish) VALUES (1, 1, 2, '2022-04-16T13:00:00+0100', '2022-04-16T14:00:00+0100');
INSERT INTO camshine.task_assignment (id, task_id, employee_id, start, finish) VALUES (2, 1, 3, '2022-04-16T13:00:00+0100', '2022-04-16T14:00:00+0100');
INSERT INTO camshine.task_assignment (id, task_id, employee_id, start, finish) VALUES (3, 2, 3, '2022-04-16T13:00:00+0100', '2022-04-16T14:00:00+0100');



create table work_rights_detail
(
    account_id int         not null
        primary key,
    ni         varchar(32) not null,
    wages_rate float       not null,
    work_limit int(3)      not null
);

INSERT INTO camshine.work_rights_detail (account_id, ni, wages_rate, work_limit) VALUES (2, 'NI', 10, 48);
INSERT INTO camshine.work_rights_detail (account_id, ni, wages_rate, work_limit) VALUES (3, 'NI', 11.5, 48);


create table additional_cost
(
    task_id int          not null
        primary key,
    tag     varchar(255) not null,
    cost    float        not null,
    created varchar(255) not null
);

INSERT INTO camshine.additional_cost (id, task_id, tag, cost, created) VALUES (1, 1, 'Additional cost', 49.5, '2022-04-15T17:07:56+0100');
INSERT INTO camshine.additional_cost (id, task_id, tag, cost, created) VALUES (2, 2, 'tag', 10.5, '');
INSERT INTO camshine.additional_cost (id, task_id, tag, cost, created) VALUES (3, 1, 'tag', 0.5, '');
INSERT INTO camshine.additional_cost (id, task_id, tag, cost, created) VALUES (4, 1, 'Additional cost', 50, '2022-04-15T17:07:56+0100');