SQL Server在使用COUNT()时显示所有记录

Question

我需要为调查申请创建一个报告，调查表格有10个问题，每个问题有4个选项，所选选项记录在响应表中。

正如我所说，每个问题有4个选项，所以每个问题都应该与其选项一起显示4次，如果没有用户选择该选项，我仍然需要显示该选项，并将0显示为TotalUsers

Select V.[Question] as [Question], O.[Option] AS [Option] , COUNT(R.[OptionId]) AS [TotalUsers]
FROM dbo.[VitalSignQuestions] V
LEFT JOIN dbo.[VitalSurveyOptions] O
ON V.[QuestionId] = O.[QuestionId]
LEFT JOIN dbo.[Response] R
ON R.[OptionId] = O.[OptionId]
LEFT JOIN dbo.[Employee] E
ON R.[EmployeeId] = E.[EmployeeId]
WHERE E.[IsActive] = 1 AND E.[RoleId] = @RoleId

现在来讨论我的问题，如果至少有1个用户选择了选项，如果没有用户选择该选项，我仍然需要显示该选项，并将TotalUsers显示为0

由于我有10个问题，每个问题有4个选项，我的输出应该有40行

Answer 1

您必须使用子查询来获取响应的计数。

以下查询将有助于

Select  V.[Question] as [Question], O.[Option] AS [Option] 
, (SELECT COUNT(1) FROM dbo.[Response] R WHERE R.[OptionId] = O.[OptionId] ) AS [TotalUsers]

FROM    dbo.[VitalSignQuestions] V
LEFT JOIN   dbo.[VitalSurveyOptions] O ON   V.[QuestionId] = O.[QuestionId]
--LEFT JOIN dbo.[Response] R ON R.[OptionId] = O.[OptionId]
--LEFT JOIN dbo.[Employee] E ON R.[EmployeeId] = E.[EmployeeId]
WHERE E.[IsActive] = 1 AND E.[RoleId] = @RoleId

您可能需要根据您的情况添加更多where子句。

快乐编码