如何解决以下嵌套连接函数错误

Question

基本数据：

-----------------------
Date      | ID |   PL |
-----------------------
16.09.2019| 21 |    0 |
17.09.2019| 21 |    0 |
18.09.2019| 21 |    1 |
19.09.2019| 21 |    2 |

预期产出：

-----------------------------------
Date      | ID |   PL |  ZC | TC |
-----------------------------------
16.09.2019| 21 |    0 |  2  |  4  |
17.09.2019| 21 |    0 |  2  |  4  |
18.09.2019| 21 |    1 |  2  |  4  |
19.09.2019| 21 |    2 |  2  |  4  |

代码使用的是单个联接函数，而不是下面的代码。

SELECT [4G].*,Z.ZC,T.TC
FROM 4G
LEFT JOIN (SELECT COUNT([4G].[ID]) AS ZC, [4G].[ID]
FROM 4G
WHERE [4G].[PL] =0
GROUP BY [4G].[ID])
AS Z
ON [4G].[ID] = Z.[ID]
LEFT JOIN (SELECT COUNT([4G].[ID]) AS TC, [4G].[ID]
FROM 4G
GROUP BY [4G].[ID])
AS T
ON [4G].[ID] = T.[ID];

显示的错误是：

"Syntax Error(missing operator) in query expression
'[4G].[ID] = Z.[ID]
LEFT JOIN (SELECT COUNT([4G].[ID]) AS TC, [4G].[ID]
FROM 4G
GROUP BY [4G].[ID])
AS T
ON [4G].[ID] = T.[ID]'

Answer 1

Access需要对每对连接表在另一个联接之前使用括号：

SELECT [4G].*,Z.ZC,T.TC
FROM ([4G]
LEFT JOIN (
  SELECT COUNT([4G].[ID]) AS ZC, [4G].[ID]
  FROM [4G]
  WHERE [4G].[PL] =0
  GROUP BY [4G].[ID]
) AS Z ON [4G].[ID] = Z.[ID])
LEFT JOIN (
  SELECT COUNT([4G].[ID]) AS TC, [4G].[ID]
  FROM [4G] 
  GROUP BY [4G].[ID]
) AS T ON [4G].[ID] = T.[ID];

结果：

Date        ID  PL  ZC  TC
16/9/2019   21  0   2   4
17/9/2019   21  0   2   4
18/9/2019   21  1   2   4
19/9/2019   21  2   2   4