mysql乘法返回错误结果

yrefmtwq 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(365)

我有两个外键相关的表。员工表如下：

+----+------------+-----------+---------------+
| id | first_name | last_name | billable_rate |
+----+------------+-----------+---------------+
|  1 | James      | Maxston   |           300 |
|  2 | Sean       | Scott     |           500 |
+----+------------+-----------+---------------+

时间表如下：

+----+----------+------------+------------+----------+-------------+
| 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 |
+----+----------+------------+------------+----------+-------------+

运行以下查询：

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;

产生以下结果：

+---------+-------------+-----------------+------------+-------------------------------------+
| project | employee_id | number_of_hours | unit_price | cost                                |
+---------+-------------+-----------------+------------+-------------------------------------+
| AIT     |           1 |               8 |        300 | 1200.000000000000000000000000000000 |
| AIT     |           2 |               4 |        500 | 2000.000000000000000000000000000000 |
+---------+-------------+-----------------+------------+-------------------------------------+

相反，我期望它会产生这样的结果：

+---------+-------------+-----------------+------------+-------------------------------------+
| project | employee_id | number_of_hours | unit_price | cost                                |
+---------+-------------+-----------------+------------+-------------------------------------+
| AIT     |           1 |               8 |        300 | 2400.000000000000000000000000000000 |
| AIT     |           2 |               4 |        500 | 2000.000000000000000000000000000000 |
+---------+-------------+-----------------+------------+-------------------------------------+

我的问题哪里出错了？

sql mysql inner-join

来源：https://stackoverflow.com/questions/63192548/mysql-multiplication-returning-wrong-result

2条答案

按热度按时间

htrmnn0y1#

在mysql中，您无法控制何时 @variable 在处理查询时更新值。所以要避免使用它们。它们会导致混乱。
您仍然可以使您的业务逻辑（在您的案例中是 cost )相当容易阅读。
尝试使用嵌套查询：类似这样的查询。

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_id

mysql的queryplanner在处理这种嵌套查询方面做得相当好，因此您不必太担心性能。
如果需要，可以将内部查询定义为视图。那么你的外部查询真的很容易阅读。

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_id

要创建视图，需要

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_rate

易读是好的。

赞(0）回复(0）举报 2021-07-27

zf9nrax12#

不使用变量，一个简单的聚合可以完成您需要的处理。例如，您可以执行以下操作：

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_id

赞(0）回复(0）举报 2021-07-27

我来回答

mysql乘法返回错误结果

2条答案

相关问题

热门标签

最新问答