带有左连接语法错误的 SQL 子查询

Question

我有一个正在尝试运行的查询，但是在运行它时我不断收到语法错误。以下是给我错误的查询:

SELECT A.*, B.Total AS Assigned
FROM (
    SELECT tblSkillSets.employeeName AS Employee, tblEvents.eventName AS Event, tblProcesses.ID AS Process, tblProcesses.Priority.Value AS       Priority, tblProcesses.minutesPerItem AS Minutes
    FROM tblEvents INNER JOIN (tblSkillSets INNER JOIN tblProcesses ON tblSkillSets.eventName = tblProcesses.eventName.Value) ON tblEvents.ID = tblSkillSets.eventName
    WHERE tblProcesses.ID=27
) AS A
LEFT JOIN 
(
    SELECT tblWorkflow.assignedTo AS Employee, Sum(IIf(DateDiff('d',[openedDate],Date())=0,[itemCount]*[minutesPerItem],0)) AS Total
    FROM tblProcesses RIGHT JOIN tblWorkflow ON tblProcesses.ID = tblWorkflow.processName
    WHERE tblWorkflow.processName=27 AND (tblWorkflow.Status=1 Or tblWorkflow.Status=2)
    GROUP BY tblWorkflow.assignedTo
) AS B 
ON A.Employee = B.Employee
ORDER BY B.Total, A.Employee

奇怪的是，以下查询可以正常工作:

SELECT Query1.*, Query2.Total AS Assigned
FROM Query1 LEFT JOIN Query2 ON Query1.Employee = Query2.Employee
ORDER BY Query2.Total, Query1.Employee

其中 Query1 是:

SELECT tblSkillSets.employeeName AS Employee, tblEvents.eventName AS Event, tblProcesses.ID AS Process, tblProcesses.Priority.Value AS Priority, tblProcesses.minutesPerItem AS Minutes
FROM tblEvents INNER JOIN (tblSkillSets INNER JOIN tblProcesses ON tblSkillSets.eventName = tblProcesses.eventName.Value) ON tblEvents.ID = tblSkillSets.eventName
WHERE tblProcesses.ID=27;

和 Query2 是:

SELECT tblWorkflow.assignedTo AS Employee, Sum(IIf(DateDiff('d',[openedDate],Date())=0,[itemCount]*[minutesPerItem],0)) AS Total
FROM tblProcesses RIGHT JOIN tblWorkflow ON tblProcesses.ID = tblWorkflow.processName
WHERE tblWorkflow.processName=27 AND (tblWorkflow.Status=1 Or tblWorkflow.Status=2)
GROUP BY tblWorkflow.assignedTo;

我所做的只是将组成 Query1 和 Query2 的代码用作子查询，但是在运行时它不起作用。

**编辑:

还有一件事是这个查询也有效:

SELECT A.*, Query2.Total AS Assigned
FROM (
    SELECT tblSkillSets.employeeName AS Employee, tblEvents.eventName AS Event, tblProcesses.ID AS Process, tblProcesses.Priority.Value AS Priority, tblProcesses.minutesPerItem AS Minutes
    FROM tblEvents INNER JOIN (tblSkillSets INNER JOIN tblProcesses ON tblSkillSets.eventName = tblProcesses.eventName.Value) ON tblEvents.ID = tblSkillSets.eventName
    WHERE tblProcesses.ID=27
) AS A
LEFT JOIN Query2 
ON A.Employee = Query2.assignedTo
ORDER BY Query2.Total, A.Employee

这让我认为该错误与子查询的左连接或第二个查询本身有关。虽然不太确定。

我得到的确切错误是: 查询中的语法错误。不完整的查询子句。

以下是表格:

tbl事件

ID  eventName                eventAbbr  deskName    Notes 
1   Partial Call             PC         Redemption   
2   Full Call                FC         Redemption   
3   Maturity                 MAT        Redemption   
4   UIT Final Payment        UFNL       Redemption   
5   Prerefunding/Defeasement PRF/DEF    Redemption   
6   UIT Distribution         UIT        Dividend     
7   Equity Distribution      EQTY       Dividend

tblProcesses

ID  processName             processAbbr     eventName                               minutesPerItem Priority assignAll   dataSource      Notes
17  DRV Coding (Account)    DRVCDA          UIT Distribution                        0.025                   No          Document Direct 
18  DRV Buys                DMBUY           Equity Distribution                     0.35           !        Yes         Document Direct 
19  Liquidation Exceptions  LIQEXC          Equity Distribution                     0.085                   Yes         Document Direct 
20  CMV                     CMV             UIT Distribution, Equity Distribution   30                      No          BMC Jobs    
21  As of Trades            ASOFTR          UIT Distribution, Equity Distribution   5              !        No          Document Direct 
22  Dividend Exceptions     DVEXC           Equity Distribution                     1                       No          Document Direct 
23  DRV Buys (Previous Day) PRVBUY          Equity Distribution                     0.15                    Yes         Document Direct 
24  DRV Coding (Security)   DRVCDS          Equity Distribution                     10                      No          Document Direct 
25  Upcoming Elections      UPCMEL          Equity Distribution                     5              !        No          Document Direct 
26  Long Short              LNGSHRT         UIT Distribution, Equity Distribution   2                       No          SQL Server  
27  Sold Lottery Shares     SLDLTSH         Partial Call                            1                       No          Document Direct 
28  Upcoming Finals         UPCMFN          UIT Final Payment                       1              !        No          Document Direct 
29  Remove Inventory        RMVINV          UIT Final Payment                       0.5                     No          Document Direct 

tblSkillSets

employeeName    deskName    eventName
300             Dividend    Equity Distribution
300             Dividend    UIT Distribution
305             Dividend    UIT Distribution
305             Dividend    Equity Distribution
342             Redemption  Full Call
342             Redemption  Maturity
342             Redemption  Partial Call
342             Redemption  UIT Final Payment
342             Redemption  Prerefunding/Defeasement
345             Redemption  Maturity
345             Redemption  Full Call
345             Redemption  UIT Final Payment
345             Redemption  Partial Call
345             Redemption  Prerefunding/Defeasement
346             Dividend    UIT Distribution
346             Dividend    Equity Distribution
348             Redemption  Partial Call
348             Redemption  Full Call
348             Redemption  Maturity
348             Redemption  UIT Final Payment
348             Redemption  Prerefunding/Defeasement
350             Dividend    UIT Distribution
350             Dividend    Equity Distribution

tbl工作流

ID      assignedTo  eventName   processName         itemCount   estimatedMinutes    Status  Priority    openedBy    openedDate
233382  348         Maturity    Maturity Exceptions 8           24                  3                   SYSTEM      1/9/2018 3:20:30 PM
233383  345         Maturity    Maturity Exceptions 8           24                  3                   SYSTEM      1/9/2018 3:20:30 PM
233384  342         Maturity    Maturity Exceptions 7           21                  3                   SYSTEM      1/9/2018 3:20:30 PM

预期结果



任何帮助表示赞赏，谢谢!

Answer 1

我想出了为什么这不起作用!虽然它没有解释为什么使用实际的查询对象(Query1 和 Query2)有效，但它解决了我的问题，所以我没问题。

第二个子查询中的分组导致了问题。我删除了第二个子查询中的分组，然后在作为两个子查询的聚合的最终查询中执行了分组。以下是解决该问题的工作查询:

SELECT A.Employee, A.Event, A.Process, A.Priority, A.Minutes, Sum(B.Total) AS Assigned
FROM (
    SELECT tblSkillSets.employeeName AS Employee, tblSkillSets.eventName AS Event, tblProcesses.ID AS Process, tblProcesses.Priority.Value AS Priority, tblProcesses.minutesPerItem AS Minutes 
    FROM tblEvents INNER JOIN (tblSkillSets INNER JOIN tblProcesses ON tblSkillSets.eventName = tblProcesses.eventName.Value) ON tblEvents.ID = tblSkillSets.eventName 
    WHERE tblProcesses.ID=27
) AS A 
LEFT JOIN (
    SELECT tblWorkflow.assignedTo, [itemCount]*[minutesPerItem] AS Total
    FROM tblProcesses RIGHT JOIN tblWorkflow ON tblProcesses.ID = tblWorkflow.processName
    WHERE (tblWorkflow.Status=1 Or tblWorkflow.Status=2) 
    AND DateDiff('d',[openedDate],Date())=0
) AS B 
ON A.Employee = B.assignedTo
GROUP BY A.Employee, A.Event, A.Process, A.Priority, A.Minutes, A.Employee
ORDER BY Sum(B.Total), A.Employee;

所以基本上 Access 不能组合两个子查询，其中一个是聚合，另一个不是。为了解决这个问题，使两个子查询都成为非聚合查询，将它们组合起来，并在最终查询上执行聚合。