我正在尝试计算问卷系统的答复。我想在一张表中显示结果(问题,选项,响应数)。我编写了一个查询,该查询工作得很好,但是它不会显示所有选项,并且如果没有任何响应。
我的查询
SELECT R.QuestionID, Q.QuestionName, A.OptionName, COUNT(R.OptionID) AS Responses, A.OptionID
FROM Response AS R
INNER JOIN
Question AS Q ON Q.QuestionID = R.QuestionID
INNER JOIN
Option AS A ON R.OptionID = A.OptionID
WHERE (R.QuestionnaireID = 122)
GROUP BY R.QuestionID, Q.QuestionName, A.OptionName, R.OptionID, A.OptionID
数据库结构:
表定义
CREATE TABLE [dbo].[Questionnaire] (
[QuestionnaireID] INT IDENTITY (1, 1) NOT NULL,
[QuestionnaireName] NVARCHAR (100) NOT NULL,
PRIMARY KEY CLUSTERED ([QuestionnaireID] ASC),
);
CREATE TABLE [dbo].[Question] (
[QuestionID] INT IDENTITY (1, 1) NOT NULL,
[QuestionnaireID] INT NOT NULL,
[QuestionName] NVARCHAR (250) NOT NULL,
PRIMARY KEY CLUSTERED ([QuestionID] ASC),
CONSTRAINT [FK_Question_Questionnaire] FOREIGN KEY ([QuestionnaireID]) REFERENCES [dbo].[Questionnaire] ([QuestionnaireID])
);
CREATE TABLE [dbo].[Option] (
[OptionID] INT IDENTITY (1, 1) NOT NULL,
[QuestionID] INT NOT NULL,
[OptionName] NVARCHAR (150) NOT NULL,
PRIMARY KEY CLUSTERED ([OptionID] ASC),
CONSTRAINT [FK_Option_Question] FOREIGN KEY ([QuestionID]) REFERENCES [dbo].[Question] ([QuestionID])
);
CREATE TABLE [dbo].[Response] (
[ResponseID] INT IDENTITY (1, 1) NOT NULL,
[QuestionnaireID] INT NOT NULL,
[QuestionID] INT NOT NULL,
[Val] NVARCHAR (150) NOT NULL,
[OptionID] INT NULL,
PRIMARY KEY CLUSTERED ([ResponseID] ASC),
CONSTRAINT [FK_Response_Option] FOREIGN KEY ([OptionID]) REFERENCES [dbo].[Option] ([OptionID]),
CONSTRAINT [FK_Response_Question] FOREIGN KEY ([QuestionID]) REFERENCES [dbo].[Question] ([QuestionID]),
CONSTRAINT [FK_Response_Questionnaire] FOREIGN KEY ([QuestionnaireID]) REFERENCES [dbo].[Questionnaire] ([QuestionnaireID])
);
当前数据:
insert into questionnaire values ('ASP.NET questionnaire');
insert into questionnaire values('TEST questionnaire');
insert into question values (2, 'rate our services');
insert into question values (2, 'On scale from 1 to 5, how much youre sleepy?');
insert into question values (2, 'how are you today');
insert into [Option] values (1, 'good');
insert into [Option] values (1, 'bad');
insert into [Option] values (1, 'medium');
insert into [Option] values(2, '1');
insert into [Option] values(2, '2');
insert into [Option] values(2, '3');
insert into [Option] values(2, '4');
insert into [Option] values(2, '5');
insert into [option] values (3, 'fine');
insert into [option] values (3, 'great');
insert into [option] values (3, 'not bad');
insert into [option] values (3, 'bad');
insert into response values(2, 1, 'good', 1);
insert into response values(2, 1, 'good', 1);
insert into response values(2, 1, 'bad', 2);
insert into response values(2, 1, 'good', 1);
insert into response values(2, 2, '1', 4);
insert into response values(2, 2, '3', 3);
insert into response values(2, 2, '4', 5);
insert into response values(2, 2, '5', 8);
所需的输出
SQL小提琴
LEFT JOIN
如果您想display all the options and if there are no responses for them
喜欢,则需要使用
编辑
我已经根据您的SQL提琴更新了答案。它可以在SQL Fiddle中工作,并为您提供所需的输出。
SELECT Q.QuestionName AS Question,
A.OptionName AS [Option],
COUNT(R.OptionID) AS Responses
FROM Question AS Q
INNER JOIN
[Option] AS A ON A.questionID = Q.questionID
LEFT JOIN
Response AS R ON Q.QuestionID = R.QuestionID AND R.OptionId=A.Optionid
WHERE (Q.QuestionnaireID = 2)
GROUP BY Q.QuestionID, Q.QuestionName, A.OptionName
ORDER BY Q.QuestionName,A.OptionName
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句