SELECT A.CateMainName, COUNT(B.ProjectNo) AS ProjCnt FROM [tblProject] As B INNER JOIN [tblBasCategory] As A
On A.CateMainID = 3 and B.CateMainID =3 and A.CateSubID=B.CateSubID and B.ReleaseDate is NULL GROUP BY A.CateMainID,A.CateMainName order by A.CateMainID
结果 :
CateMainName ProjCnt
Improve         66

SELECT A.CateMainName, COUNT(B.ProjectNo) AS ProjCnt FROM [tblProject] As B INNER JOIN [tblBasCategory] As A
On A.CateMainID =3 and B.CateMainID =3 and A.CateSubID=B.CateSubID and B.ReleaseDate is not NULL GROUP BY A.CateMainID,A.CateMainName order by A.CateMainID
结果 :
CateMainName ProjCnt
Improve         5

目标 :
CateMainName ProjCnt
Improve         66
Improve         5

有人教了，又学到一招，用UNION

又学到了用left JOIN可以使COUNT(*)显示搜寻结果为0的值