SQL查询问题：如何合并两个列表 [英] SQL Query problem: How to merge two lists

问题描述

我有一个参与者和答案的基表，具有以下结构：

ParticipantId，BusUnitId，QuestionNum， c $ c>

在这个表中，QuestionNum的范围是从1到6.我还有另外两个表，将QuestionNum链接到实际的问题表， BusUnitQuestions和ParticipantQuestions。对于每个QuestionNum，我必须根据QuestionId得到实际的问题文本。

BusUnitId，QuestionId
ParticipantId，QuestionId

现在假设QuestionNum从1到6的记录。BusUnitQuestions有3条记录，因此QuestionNum 1到3必须从BusUnitQuestions加入QuestionId的问题，并且QuestionNum 4至6必须从ParticipantQuestions加入QuestionId的问题。我假设我需要在BusUnitQuestions的子查询中使用ROW_NUMBER（）加入我的答案表，但是我失去了。

如果任何人都明白我，您有什么建议吗？

下面是一个示例设置。在这里，参与者回答了5个问题（1到5）。前三个问题由参与者的部门设置，最后两个由参与者选择。实际上，有超过5个问题供部门和参与者选择。我需要使用在Answers表中与QuestionNum对应的DepartmentQuestions和ParticipantQuestions中的行数，将问题表添加到Answers表。

  create table答案（AnswerId int identity（1,1），ParticipantId int，DeptId int，QuestionNum int，AnswerScore int）
 create table Dept_Question（DqId int identity（1,1），DeptId int，QuestionId int） 
 create table Particpant_Question（PqId int identity（1,1），ParticipantId int，QuestionId int）
 create table问题（QuestionId int identity（1,1），QuestionText nvarchar（200））
 
插入问题（QuestionText）值（'What's a duck？'）
 insert问题（QuestionText）值（'你多少钱？'）
 insert '
 insert $'$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $。 
 insert Dept_Question（DeptId，QuestionId）values（3，3）
 insert Dept_Question（DeptId，QuestionId）values（3,4）
插入Dept_Question ）
 
插入Particpant_Question（ParticipantId，QuestionId）值（1,2）
插入Particpant_Question（ParticipantId，QuestionId）值（1,4）
 
 insert 
插入Answers（ParticipantId，DeptId，QuestionNum，AnswerScore）值（1,3,2,99）
 insert Answers（ParticipantId，DeptId，QuestionNum， 
插入Answers（ParticipantId，DeptId，QuestionNum，AnswerScore）值（1,3,4,54）
插入Answers（ParticipantId，DeptId，QuestionNum， ParticipantId，DeptId，QuestionNum，AnswerScore）values（1,3,5,72）
  

从我的理解，你有一个单一的答案列表，但问题可以是在几个不同的表中的任何一个。有两种方法可以获得这个 - 或者几个LEFT JOIN与ISNULL，或者你可以创建一个VIEW，它是一个UNION的问题表和JOIN。

如果只是这一个查询，并且你不会在未来添加更多的问题类型，第一个可能更容易，但如果你要经常这样做（添加问题类型或查询数据）

方法1： / p>

  SELECT a.ParticipantId，a.QuestionId，a.AnswerId，ISNULL（q1.QuestionText，q2.QuestionText，NULL）as QuestionText 
 FROM答案a 
 LEFT 
 JOIN问题1 q1 / * BusinessUnitQuestions？ * / 
 ON a.QuestionId = q1.QuestionId 
 LEFT 
 JOIN Question2 q2 / * ParticipantQuestions？ * / 
 ON a.QuestionId = q2.QuestionId 
  

这样，问题文本

首先，使用UNION创建一个包含所有问题的视图，像这样（根据需要添加更多联合）：

  CREATE VIEW AllQuestions 
 AS 
 SELECT QuestionId，QuestionText 
 FROM Question1 
 UNION ALL 
 SELECT QuestionId，QuestionText 
 FROM Question2 
  

然后，您可以使用第一个查询的简化版本中的视图：

  SELECT a.ParticipantId，a.QuestionId，a.AnswerId，q.QuestionText 
 FROM a a 
 JOIN AllQuestions q 
 ON a.QuestionId = q.QuestionId 
  

I have a base table of Participants and Answers, with the following structure:

ParticipantId, BusUnitId, QuestionNum, Answer.

In this table, QuestionNum ranges, say, from 1 to 6. I also have two other tables that sort of link QuestionNum to the actual question table, BusUnitQuestions, and ParticipantQuestions. For each QuestionNum, I must get the actual question text, based on QuestionId.

BusUnitId, QuestionId ParticipantId, QuestionId

Now assuming records with QuestionNum from 1 to 6. BusUnitQuestions has 3 records, so QuestionNum 1 to 3 must join to Question on the QuestionId's from BusUnitQuestions, and QuestionNum 4 to 6 must join to Question on the QuestionId's from ParticipantQuestions. I assume I need to use ROW_NUMBER() in the subquery from BusUnitQuestions to join to my answer table, but I'm lost after that.

If anyone at all understands me, do you have any suggestions?

Below is a sample setup. In this, the particpant has answered 5 questions (1 to 5). The first three of these questions are set by the participant's department, and the last two chosen by the participant. There are in reality a lot more than 5 questions for the department and participant to choose from. I need to join the Questions table to the Answers table, using the row numbers in DepartmentQuestions and ParticipantQuestions corresponding to QuestionNum in the Answers table.

create table Answers (AnswerId int identity(1,1), ParticipantId int, DeptId int, QuestionNum int, AnswerScore int)
create table Dept_Question (DqId int identity(1,1), DeptId int, QuestionId int)
create table Particpant_Question (PqId int identity(1,1), ParticipantId int, QuestionId int)
create table Questions (QuestionId int identity(1,1), QuestionText nvarchar(200))

insert Questions (QuestionText) values ('What is a duck?')
insert Questions (QuestionText) values ('How much do you weigh?')
insert Questions (QuestionText) values ('Why does orange fit?')
insert Questions (QuestionText) values ('Who pokes the fish?')
insert Questions (QuestionText) values ('Why no cow bells?')

insert Dept_Question (DeptId, QuestionId) values (3, 3)
insert Dept_Question (DeptId, QuestionId) values (3, 4)
insert Dept_Question (DeptId, QuestionId) values (3, 1)

insert Particpant_Question(ParticipantId, QuestionId) values (1, 2)
insert Particpant_Question(ParticipantId, QuestionId) values (1, 4)

insert Answers (ParticipantId, DeptId, QuestionNum, AnswerScore) values (1, 3, 1, 63)
insert Answers (ParticipantId, DeptId, QuestionNum, AnswerScore) values (1, 3, 2, 89)
insert Answers (ParticipantId, DeptId, QuestionNum, AnswerScore) values (1, 3, 3, 44)
insert Answers (ParticipantId, DeptId, QuestionNum, AnswerScore) values (1, 3, 4, 54)
insert Answers (ParticipantId, DeptId, QuestionNum, AnswerScore) values (1, 3, 5, 72)

解决方案

From what I understand, you have a single list of answers, but the question could be in any of a couple of different tables. There are two approaches you could take to get this one - either a couple of LEFT JOINs with ISNULL, or you could create a VIEW that's a UNION of your question tables and JOIN to that.

If it's just this one query and you won't be adding more question types in the future, the first is probably easier, but if you're going to be doing this often (adding question types or querying the data), I'd go with method 2 since you'll only have to maintain the view and not your original queries.

METHOD 1:

SELECT a.ParticipantId, a.QuestionId, a.AnswerId, ISNULL(q1.QuestionText, q2.QuestionText, NULL) as QuestionText
  FROM Answers a
  LEFT
  JOIN Question1 q1 /* BusinessUnitQuestions? */
    ON a.QuestionId = q1.QuestionId
  LEFT
  JOIN Question2 q2 /* ParticipantQuestions? */
    ON a.QuestionId = q2.QuestionId

This way, the question text will be pulled regardless of which table it appears in.

METHOD 2:

First, create a view with a UNION of all your questions, like this (add as many more unions as you want):

CREATE VIEW AllQuestions
AS
SELECT QuestionId, QuestionText
  FROM Question1
 UNION ALL
SELECT QuestionId, QuestionText
  FROM Question2

Then, you can use the view in a simplified version of the first query:

SELECT a.ParticipantId, a.QuestionId, a.AnswerId, q.QuestionText
  FROM Answers a
  JOIN AllQuestions q 
    ON a.QuestionId = q.QuestionId

这篇关于SQL查询问题：如何合并两个列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！