SQL表联接查询

埃尔斯

我正在尝试计算问卷系统的答复。我想在一张表中显示结果(问题,选项,响应数)。我编写了一个查询,该查询工作得很好,但是它不会显示所有选项,并且如果没有任何响应。

我的查询

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

数据库结构

  • 问卷(questionnaireID PK,问卷名)
  • 问题(questionID PK,问卷ID FK,问卷名称)
  • 选项(OptionID PK,questionID FK,optionName)
  • 响应(ResponseID PK,问卷ID FK,问题ID FK,值)

表定义

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小提琴

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] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

sql查询联接条件与联接表

来自分类Dev

SQL查询联接多个表

来自分类Dev

用查询SQL联接3个表

来自分类Dev

来自联接查询的sql更新表

来自分类Dev

SQL查询联接四个表

来自分类Dev

SQL查询,联接两个表

来自分类Dev

SQL查询出多个联接表数据

来自分类Dev

在SQL查询中联接表以进行搜索

来自分类Dev

SQL查询与临时表的内部联接

来自分类Dev

通过联接表进行复杂的SQL查询

来自分类Dev

多个表的SCCM SQL查询联接

来自分类Dev

SQL查询联接四个表

来自分类Dev

SQL查询联接5个表

来自分类Dev

SQL联接查询与三个表

来自分类Dev

基于查询表的SQL Server内部联接

来自分类Dev

SQL查询,联接两个表

来自分类Dev

SQL Server 2008查询-表联接

来自分类Dev

SQL子查询和表联接问题

来自分类Dev

与表用户的MariaDB SQL查询内部联接表消息

来自分类Dev

查询表:复杂联接

来自分类Dev

查询表:复杂联接

来自分类Dev

联接表中子查询中的SQL Server 2012最大日期

来自分类Dev

复杂的SQL查询-联接表中的字段总和

来自分类Dev

SQL查询内部联接表,打印到HTML <select>标记

来自分类Dev

来自不同fdb数据库的SQL查询联接表

来自分类Dev

哪里将联接多个表的SQL查询放在哪里?

来自分类Dev

如何使用SQL联接3个表(1个查询)

来自分类Dev

对LINQ C#的SQL查询[联接多个表]

来自分类Dev

SQL查询-完全联接多个表,但返回NULL结果