MySQL-使用JOIN和WHERE确定平均时间 [英] MySQL - Using JOIN and WHERE to determine average hours

问题描述

我正在使用依赖 next.tech课程学习MySQL. /i.imgur.com/Wd5ezgd.png"rel =" nofollow noreferrer>以下架构:

I am learning MySQL using a next.tech course that relies on the following schema:

我当前的任务是找到一个特定项目的平均工作小时数:这些小时数在 project_employees 数据库下，而项目名称(华盛顿大道理发师)在下项目数据库.

My current task is to find the average number of hours worked on one specific project: the hours are under the project_employees database, while the project name (Washington Avenue Barber) is under the projects database.

我尝试按以下方式使用JOIN和WHERE函数来尝试返回平均工作小时数:

I have attempted to use the JOIN and WHERE functions as follow to try and return the average number of hours worked:

SELECT AVG(hours) FROM project_employees
WHERE name = 'Washington Avenue Barber'
JOIN  projects 
ON  project_employees.employee_id = projects.id;

但是，我收到以下错误消息:

However, I receive the following error:

第1行的错误1064(42000):您的SQL语法有错误； 检查与您的MySQL服务器版本相对应的手册 在'JOIN项目附近使用正确的语法 第3行的project_employees.employee_id = projects.id'

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN projects ON project_employees.employee_id = projects.id' at line 3

我尝试了许多不同的函数来尝试获得正确的结果，但是只有以下函数返回一个值，并且它是不正确的(应该返回381.115555，但是下面的代码返回743.300003):

I have tried a number of different functions to try and obtain the correct result, however only the following returns a value and it is incorrect (it should return 381.115555, but the code below returns 743.300003):

SELECT AVG(hours) FROM project_employees
JOIN  projects
ON  project_employees.employee_id = projects.id
WHERE name = 'Washington Avenue Barber';

推荐答案

SQL查询的语法似乎是第一个错误的主要问题.

The syntax of your SQL query seems to be the main problem behind the first error.

您正在执行SELECT-FROM-WHERE-JOIN，但实际顺序应为SELECT-FROM-JOIN-WHERE.

you are doing SELECT-FROM-WHERE-JOIN, but the actual sequence should be SELECT-FROM-JOIN-WHERE.

您的第二个查询(返回错误的值)具有正确的语法，这就是为什么它返回某些内容的原因.

Your second query (which is returning a wrong value) has the correct syntax, which is why it returns something.

第二，您应该将projects.id与project_employees.project_id而不是project_employees.employee_id进行比较.因此，您的查询应类似于-

Secondly, you should be comparing projects.id with project_employees.project_id, not project_employees.employee_id. Therefore, your query should be something like -

SELECT AVG(hours) FROM project_employees
JOIN  projects
ON  project_employees.project_id = projects.id
WHERE name = 'Washington Avenue Barber';

这篇关于MySQL-使用JOIN和WHERE确定平均时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！