sql - 谁能发现我写的这个 MS Access SQL 代码中的 SQL 错误？

Question

以下是表格：表格和关系

我的目标是说明服务代表名字、服务代表姓氏和剩余小时数，这些小时数是通过减去 [服务请求].[总小时数] - [服务请求数].[花费小时数] 计算得出的。

  SELECT
[Service Rep].[First Name],
[Service Rep].[Last Name],
[Service Requests].[Total Hours] - [Service Requests].[Hours Spent] AS [Hours Remaining]
FROM [Service Rep]  INNER JOIN Client ON
[Service Rep].[Service Rep Number] = Client.[Service Rep Number] 
INNER JOIN  [Service Requests] ON 
Client.[Client Number] = [Service Requests].[Client Number] 
INNER JOIN [Service Requests] ON
[Service Requests].[Service Code] = [Service].[Service Code] 
group by [Service Rep].[First Name],
[Service Rep].[Last Name];

错误消息 我收到这个 - 在查询表达式。（错误 3075）

谁能告诉我我的 SQL 查询出了什么问题？

由于 Gordon Linoff 的帮助，正确的代码：

SELECT [Service Rep].[First Name], [Service Rep].[Last Name],
       SUM([Service Requests].[Total Hours] - [Service Requests].[Hours Spent]) AS [Hours Remaining]
FROM (([Service Rep]  INNER JOIN
       Client
       ON [Service Rep].[Service Rep Number] = Client.[Service Rep Number]
      ) INNER JOIN
      [Service Requests]
      ON Client.[Client Number] = [Service Requests].[Client Number] 
     ) INNER JOIN
     [Service]
     ON [Service Requests].[Service Code] = [Service].[Service Code] 
GROUP BY [Service Rep].[First Name], [Service Rep].[Last Name]
ORDER BY SUM([Service Requests].[Total Hours] - [Service Requests].[Hours Spent]);

标签： sqlms-access-2016

Answer 1

您的代码有多个问题。不知道你在做什么，有三个明显的：

• MS Access 需要为多个JOINs 加上笨拙的括号。
• 您SELECT的列与列不一致GROUP BY。
• GROUP BY正在引用未定义的表别名。

也许你想要：

SELECT [Service Rep].[First Name], [Service Rep].[Last Name],
       SUM([Service Requests].[Total Hours] - [Service Requests].[Hours Spent]) AS [Hours Remaining]
FROM (([Service Rep]  INNER JOIN
       Client
       ON [Service Rep].[Service Rep Number] = Client.[Service Rep Number]
      ) INNER JOIN
      [Service Requests]
      ON Client.[Client Number] = [Service Requests].[Client Number] 
     ) INNER JOIN
     [Service Requests]
     ON [Service Requests].[Service Code] = [Service].[Service Code] 
GROUP BY [Service Rep].[First Name], [Service Rep].[Last Name];