我对以下 SQL 嵌套查询有问题

Question

无法识别 a 的嵌套查询。

SELECT
  a.Emp_No,
  SUM(b.[Reg_Hours]) AS [Clocked Regular Hours],
  SUM(a.[Regular_Hours]) AS [Paid Regular Hours],
  SUM(b.[OT_Hrs]) AS [Clocked OT Hours],
  SUM(a.[Overtime_Hours]) AS [Paid Overtime Hours]
FROM (SELECT
       Lenrold_Payroll_Transactions.Employee_No AS Emp_No,
       SUM(Regular_Hours) AS [Paid Regular Hours],
       SUM(Overtime_Hours) AS [Paid Overtime Hours]
     FROM Lenrold_Payroll_Transactions
     GROUP BY Lenrold_Payroll_Transactions.Employee_No) AS a,
     Lenrold_Payroll_Time AS b
WHERE a.Employee_No = b.Emp_No;

Answer 1

我的猜测是你的SQL引擎不喜欢你正在使用的隐式连接语法。 使用显式联接重写查询：

SELECT
    a.Emp_No,
    b.[Clocked Regular Hours],
    a.[Paid Regular Hours],
    b.[Clocked OT Hours],
    a.[Paid Overtime Hours]
FROM 
(
    SELECT
        Employee_No,
        SUM(Regular_Hours) AS [Paid Regular Hours],
        SUM(Overtime_Hours) AS [Paid Overtime Hours]
    FROM Lenrold_Payroll_Transactions 
    GROUP BY Employee_No
) AS a
INNER JOIN
(
    SELECT
        Emp_No,
        SUM(Reg_Hours) AS [Clocked Regular Hours],
        SUM(OT_Hrs) AS [Clocked OT Hours]
    FROM Lenrold_Payroll_Time
    GROUP BY Emp_No
) AS b 
    ON a.Employee_No = b.Emp_No;