MS-Access中的SQL:使用COUNT，JOIN并返回0 [英] SQL in MS-Access: Using COUNT, JOIN and returning 0s

问题描述

道歉，但尽管网站上有一些示例，但我还是无法正常工作.

Apologies for posting this but although there are a few examples on the site, I just can't get mine to work.

所以我有两个表，如下所示:

So I have two tables as follows:

电话表

ID | Name | GradeID
1    Richard   1
2    Allan     1
3    Peter

我也有一个成绩表:

ID | Name
1    1
2    2
3    3
4    4
5    5

无论如何，我正尝试使用COUNT()和LEFT JOIN来查找通过电话查询表在电话表中找到每个年级的次数，包括使用以下查询返回包括0的任何分数:

Anyway I'm trying to use COUNT() and LEFT JOIN to find out the number of times each grade is found in the Telephony table, including returning any which are 0, by using the following query:

SELECT telephony.GradeID, COUNT(*) AS Total
FROM telephony LEFT JOIN grade 
ON telephony.GradeID = grade.ID
GROUP BY telephony.GradeID
ORDER BY 1;

此查询返回找到的所有成绩，但不会返回所有0项的成绩:

This query returns all found but will not return all grades with 0 entries:

Grade | Total
1       2

请帮助.我正在使用Microsoft Access 2003.

Please help. I'm using Microsoft Access 2003.

感谢您的所有帮助.效果很好.

Thanks for all your help. That's working great.

但是，当我尝试将DATE BETWEEN纳入其中时，它仅返回再次找到的成绩.

However, when I try to incorporate a DATE BETWEEN it returns only the grades found again.

有什么想法吗?

谢谢

推荐答案

由于只有成绩表包含所有可用的ID，因此您应该在SELECT中使用grade.ID而不是telephony.GradeID.尝试执行此查询:

As only Grade table contains all available IDs you should use grade.ID in your SELECT instead of telephony.GradeID. Try to execute this query:

SELECT grade.ID, COUNT(telephony.ID) FROM grade
LEFT JOIN telephony ON telephony.GradeID = grade.ID
GROUP BY grade.ID
ORDER BY 1;

这篇关于MS-Access中的SQL:使用COUNT，JOIN并返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！