MySQL员工数据库

Question

我在设计执行以下操作的查询时遇到问题:

使用以下数据库架构列出员工姓名、员工编号及其各自的每个项目的总收入:

department(primary key(deptName), deptName, deptCity) 
employee(primary key(empNum), empName, empCity)
project(primary key(projectNum), projectName, budget)
worksOn(foreign key(empNum), foreign key(projectNum), deptNum, jobTitle, startDate, earningPerProject) 

我可以显示员工姓名和员工编号，但当谈到每个员工的 PerProject 收入总额时，我迷失了。

有些员工被列出了不止一次，我意识到我必须使用聚合函数SUM()和COUNT()，但我还没弄清楚成功做到这一点的方法。

这是我到目前为止所拥有的:

SELECT DISTINCT(empName), employee.empNum, earningPerProject FROM employee, worksOn
WHERE worksOn.empNum = employee.empNum;

有人可以帮助我提供一些提示或示例查询吗？我不确定我将如何去做这件事。

Answer 1

此处，您必须使用 GROUP BY 子句和 SUM() 来计算给定员工的 PerProject 总收入。

DISTINCT 不是必需的。在您的代码中，您使用了 DISTINCT(empName) ，看起来您希望消除结果中重复的员工姓名。可能有两名员工同名，因此仅检索唯一的姓名可能会将某些员工排除在结果之外。这就是为什么我们使用 empNum 作为主键而不是名称。您实际上想要检索 empNum 和 empName 的不同组合。

您认为 worksOn 表中可能存在重复的 empNum 是正确的，因为给定的员工可以处理多个项目。 GROUP BY 会将具有相同 empNum 和 empName 的所有行分组在一起，并将它们组合成一行，从而消除对 独特。 (更多内容见下文)

在这里，我修改了您的查询以包含 SUM() 和 GROUP BY。

SELECT employee.empNum, employee.empName, SUM(worksOn.earningPerProject)
  FROM employee, worksOn
 WHERE employee.empNum = worksOn.empNum
 GROUP BY employee.empNum, employee.empName;

<小时/>

加入

FROM 子句 (FROMEmployee,worksOn) 中使用的语法是已知的，其中列出要在同一行上连接在一起并以逗号分隔的表作为隐式连接。根据 Join (SQL)，随着 SQL-92 的发布，此语法已被弃用。 .

最佳实践要求您改用称为显式联接的新语法，方法是使用 JOIN 关键字和添加的 ON 关键字来描述表之间的链接.

新的 JOIN 语法在功能上等同于旧的隐式连接语法。两者产生相同的结果。

SELECT employee.empNum, employee.empName, SUM(worksOn.earningsPerProject)
  FROM employee
  JOIN worksOn ON employee.empNum = worksOn.empNum
 GROUP BY employee.empNum, employee.empName;

<小时/>

独特

DISTINCT 是一个 SQL 关键字，它根据 SELECT 列表中的表达式消除重复的结果行。如果您仅请求一个表达式 (SELECT empCity FROMEmployee)，它将返回该表达式的唯一值(每个城市仅显示一次)。如果您请求多个表达式，它将返回这些表达式的唯一组合。

许多数据库引擎使用GROUP BY来计算DISTINCT结果，因此将它们一起使用通常是多余的。

不幸的是，您的查询包含一些合法的 SQL 语法。您将 empName 放在括号中，得到 SELECT DISTINCT (empName),employee.empNum, ...。此语法具有误导性，因为 DISTINCT 是关键字而不是函数，并且 DISTINCT 不使用此处的括号。当使用 DISTINCT 时，它适用于 SELECT 中的所有表达式。在这种情况下，删除括号不会改变含义，尽管它确实使其更加清晰。

这三个查询是等效的:

SELECT DISTINCT empName, employee.empNum, ...

SELECT DISTINCT (empName), employee.empNum, ...

SELECT DISTINCT empName, (employee.empNum), ...

SQL 中的括号可用于对表达式进行分组，通常用于在处理 <、>、=、*、/等运算符时强制执行计算顺序。在单个表达式周围放置括号不会改变其值。虽然您认为只是将 DISTINCT 用于 empName，但实际上只是将表达式 empName 括在括号中，实际上什么也没做。

您可以通过运行此查询来测试

SELECT empName FROM employee

和这个查询

SELECT (empName) FROM employee

您将看到相同的结果。