Tengo dos tablas relacionadas por una clave externa. La tabla de empleados es la siguiente:
+----+------------+-----------+---------------+ | id | first_name | last_name | billable_rate | +----+------------+-----------+---------------+ | 1 | James | Maxston | 300 | | 2 | Sean | Scott | 500 | +----+------------+-----------+---------------+La tabla de horas trabajadas es la siguiente:
+----+----------+------------+------------+----------+-------------+ | id | project | date | start_time | end_time | employee_id | +----+----------+------------+------------+----------+-------------+ | 1 | AIT | 2020-07-20 | 09:00:00 | 12:00:00 | 1 | | 2 | Axiiscom | 2020-06-20 | 15:00:00 | 17:00:00 | 1 | | 3 | AIT | 2020-07-20 | 13:00:00 | 18:00:00 | 1 | | 4 | AIT | 2020-07-01 | 11:00:00 | 14:00:00 | 2 | | 5 | AIT | 2020-06-21 | 11:00:00 | 12:00:00 | 2 | +----+----------+------------+------------+----------+-------------+Ejecutando la consulta a continuación:
SELECT project, employee_id, @hours_worked := SUM(timestampdiff(HOUR, start_time, end_time)) AS number_of_hours, @hourly_rate :=my_db.employee.billable_rate AS unit_price, @hours_worked * @hourly_rate AS cost FROM my_db.timesheet INNER JOIN my_db.employee ON my_db.employee.id = my_db.timesheet.employee_id WHERE project = "AIT" GROUP BY employee_id;da el siguiente resultado:
+---------+-------------+-----------------+------------+-------------------------------------+ | project | employee_id | number_of_hours | unit_price | cost | +---------+-------------+-----------------+------------+-------------------------------------+ | AIT | 1 | 8 | 300 | 1200.000000000000000000000000000000 | | AIT | 2 | 4 | 500 | 2000.000000000000000000000000000000 | +---------+-------------+-----------------+------------+-------------------------------------+cuando, en cambio, esperaba que arrojaría este resultado:
+---------+-------------+-----------------+------------+-------------------------------------+ | project | employee_id | number_of_hours | unit_price | cost | +---------+-------------+-----------------+------------+-------------------------------------+ | AIT | 1 | 8 | 300 | 2400.000000000000000000000000000000 | | AIT | 2 | 4 | 500 | 2000.000000000000000000000000000000 | +---------+-------------+-----------------+------------+-------------------------------------+¿Dónde me estoy equivocando en mi consulta?
En lugar de utilizar variables, una simple agregación puede realizar el procesamiento que necesita. Por ejemplo, puedes hacer:
select t.project, t.employee_id, sum(timestampdiff(HOUR, t.start_time, t.end_time)) as number_of_hours, max(e.billable_rate) as unit_price, sum(timestampdiff(HOUR, t.start_time, t.end_time)) * max(e.billable_rate) as cost from my_db.timesheet t join my_db.employee e on e.id = t.employee_id where t.project = 'AIT' group by t.project, t.employee_idEn MySQL, no tiene mucho control sobre cuándo se actualizan los valores de @variable mientras se procesan las consultas. Así que evítelos para el propósito que está usando. Conducen a la confusión.
Todavía puede hacer que su lógica comercial (en su caso, el cálculo del cost ) sea razonablemente fácil de leer.
Intente usar una consulta anidada en su lugar: algo como esto.
SELECT project, employee_id, number_of_hours, unit_price, unit_price * number_of_hours AS cost FROM ( SELECT my_db.timesheet.project, my_db.timesheet.employee_id, SUM(timestampdiff(HOUR, start_time, end_time)) AS number_of_hours, my_db.employee.billable_rate AS unit_price FROM my_db.timesheet INNER JOIN my_db.employee ON my_db.employee.id = my_db.timesheet.employee_id GROUP BY my_db.timesheet.project, my_db.timesheeet.employee_id, my_db.employee.billable_rate ) summary WHERE project = 'AIT' ORDER BY employee_idEl planificador de consultas de MySQL hace un trabajo razonablemente bueno al manejar este tipo de consultas anidadas, por lo que no tiene que preocuparse demasiado por el rendimiento.
Si lo desea, puede definir la consulta interna como una vista. Entonces su consulta externa es realmente fácil de leer.
SELECT project, employee_id, number_of_hours, unit_price, unit_price * number_of_hours AS cost FROM my_db.project_summary WHERE project = 'AIT' ORDER BY employee_idPara crear la vista, haces
CREATE VIEW my_db.project_summary AS SELECT my_db.timesheet.project, my_db.timesheet.employee_id, SUM(timestampdiff(HOUR, start_time, end_time)) AS number_of_hours, my_db.employee.billable_rate AS unit_price FROM my_db.timesheet INNER JOIN my_db.employee ON my_db.employee.id = my_db.timesheet.employee_id GROUP BY my_db.timesheet.project, my_db.timesheeet.employee_id, my_db.employee.billable_rateFácil de leer es bueno.