有人可以告诉我如何将该查询转换为可在MS Access中使用吗?
SELECT Shooters.idShooters, Shooters.firstname, Shooters.lastname, JoinedContingent.Count, JoinedShots.Count
FROM Shooters
INNER JOIN
(SELECT Shooters.idShooters, Count(Contingent.idContingent) AS Count
FROM Shooters LEFT JOIN Contingent ON Shooters.idShooters = Contingent.fidShooter
GROUP BY Shooters.idShooters)
AS JoinedContingent ON JoinedContingent.idShooters = Shooters.idShooters
INNER JOIN
(SELECT Shooters.idShooters, Count(Shots.idShots) AS Count
FROM Shooters LEFT JOIN Shots ON Shooters.idShooters = Shots.fidShooters
GROUP BY Shooters.idShooters)
AS JoinedShots ON JoinedShots.idShooters = Shooters.idShooters;
我想计算Shooter
表Contingent
和中每个外键的出现次数Shots
。结果应如下所示:
idShooters | firstname | lastname | Count | Count
____________________________________________________________
1 John Doe 0 10
2 Jane Doe 1 20
.
.
.
我认为这是您想要的:
SELECT s.idShooters, s.firstname, s.lastname,
NZ(c.Count, 0), NZ(sh.Count, 0)
FROM (Shooters as s LEFT JOIN
(SELECT c.fidShooter, Count(*) AS Count
FROM Contingent as c
GROUP BY c.fidShooter
) as c
ON s.idShooters = c.fidShooter
) LEFT JOIN
(SELECT sh.fidShooters, Count(*) AS Count
FROM Shots as sh
GROUP BY sh.fidShooters
) as sh
ON s.idShooters = sh.fidShooters;
请注意,我将外部联接移到了外部查询。实际上,子查询中不需要连接,因此不必理会。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句