我有以下代码。 我的问题是我想从另一个表名称 Employee 中检索每个员工姓名,该表名称具有类似于我在当前查询中使用的 AccessLog 表的 EmployeeID 列。 现在,当我加入这两个表时,它给出了我在标题上所说的奇怪错误。没有添加用于加入的代码 - A.EmployeeID,第一个 SELECT 中的 Employee.FirstName 和 ; 作为内部联接 A.EmployeeID 上的 Employee = Employee.EmployeeID 到最后一个 ORDER By 子句,它运行良好。我认为问题出在命名/别名上。我看到了类似的线程,但这些并没有解决我的问题。如果有人能指出我的错误,将不胜感激。
SELECT **A.EmployeeID,
Employee.FirstName,**
MonthName(Month([LogDate])) AS MonthName,
Round((Sum(Int(DateDiff("s",'00:00:00',Duration)))/3600)) AS TotalTime
FROM (
SELECT AccessLog.EmployeeID,
AccessLog.LogDate,
AccessLog.TerminalID,
AccessLog.LogTime,
Format((SELECT max(LogTime)
FROM AccessLog AS Alias
WHERE Alias.LogTime < AccessLog.LogTime
AND Alias.EmployeeID = AccessLog.EmployeeID
AND Alias.LogDate = AccessLog.LogDate
AND (Alias.TerminalID)<>"iGuard1A"
And (Alias.TerminalID)<>"iGuard1B"
AND Alias.EmployeeID = AccessLog.EmployeeID),"hh:nn:ss") AS PrevTime,
Format((ElapsedTime(iif(PrevTime = '',logtime,prevtime),[LogTime])),"hh:nn:ss") AS Duration,
AccessLog.InOut
FROM AccessLog
WHERE (((AccessLog.TerminalID)<>"iGuard1A"
And (AccessLog.TerminalID)<>"iGuard1B")
AND ((AccessLog.EmployeeID) Like "2*")
AND ((AccessLog.InOut)="OUT"))
ORDER BY AccessLog.EmployeeID, AccessLog.LogDate, AccessLog.LogTime)
**AS A INNER JOIN Employee ON A.EmployeeID= Employee.EmployeeID**
GROUP BY EmployeeID, MonthName(Month([LogDate]));
我认为这可能是GROUP BY EmployeeId
- 尝试将其更改为GROUP BY A.EmployeeId
或GROUP BY Employee.EmployeeId
。
编辑:它可能必须GROUP BY A.EmployeeId
,因为这是使用SELECT的那个。
我解决了。正如我所说,存在名称/别名问题。我必须为每个子查询中使用的同一表提供单独的别名。即使在那之后,它也在顶部选择中为名字显示另一个加重函数的错误,我也通过将其包含在子查询中来解决这个问题。因此,我的代码完整如下:
SELECT C.EmployeeID,
(SELECT Employee.FirstName& ' ' &Employee.LastName
FROM Employee
where C.EmployeeID= Employee.EmployeeID)
AS FullName,
MonthName(Month([LogDate])) AS MonthName,
Round((Sum(Int(DateDiff("s",'00:00:00',Duration)))/3600)) AS TotalTime
FROM (SELECT B.EmployeeID,
B.LogDate,
B.TerminalID,
B.LogTime,
Format((SELECT max(LogTime)
FROM AccessLog AS A
WHERE A.LogTime < B.LogTime
AND A.EmployeeID = B.EmployeeID
AND A.LogDate = B.LogDate
AND (A.TerminalID)<>"iGuard1A"
And (A.TerminalID)<>"iGuard1B"
AND A.EmployeeID = B.EmployeeID),"hh:nn:ss") AS PrevTime,
Format((ElapsedTime(iif(PrevTime = '',logtime,prevtime),[LogTime])),"hh:nn:ss") AS Duration,
B.InOut
FROM AccessLog As B
WHERE (((B.TerminalID)<>"iGuard1A"
And (B.TerminalID)<>"iGuard1B")
AND ((B.EmployeeID) Like "2*")
AND ((B.InOut)="OUT"))
ORDER BY B.EmployeeID, B.LogDate, B.LogTime)
AS C INNER JOIN Employee ON C.EmployeeID= Employee.EmployeeID
GROUP BY C.EmployeeID, MonthName(Month([LogDate]));