csv 联接从另一个表中选择的一列中的多个值

fwzugrvs  于 2023-09-28  发布在  其他
关注(0)|答案(4)|浏览(132)

给出这些简化的多项选择表,其中有时不止一个答案是正确的:

STUDENT_ANSWERS
AnswerID | StudentID | QuestionID | Answers
-------------------------------------------
       1 |         1 |          1 | C,D

QUESTION_ANSWERS
QuestionID | Answer | Text
-------------------------------------------------
         1 |      A | This is answer A
         1 |      B | B could also be correct
         1 |      C | Maybe it's C?
         1 |      D | Definitely D!

如何选择将答案转换为描述的选项?
我的开始:

SELECT *
FROM STUDENT_ANSWERS sa
LEFT OUTER JOIN QUESTION_ANSWERS qa ON qa.Answer IN sa.Answers???
 -- Doesn't seem to work as IN requires a format of ('C','D') while I have 'C,D'

所需输出:

AnswerID | StudentID | QuestionID | AnswerDescriptions
-------------------------------------------
       1 |         1 |          1 | Maybe it's C?,Definitely D!

所以描述只需要替换代码,而不是每个答案都有一行。

nuypyhwy

nuypyhwy1#

你的问题是表STUDENT_ANSWERS的结构。每个答案应该有一行:

AnswerID | StudentID | QuestionID | Answer
-------------------------------------------
       1 |         1 |          1 | C
       2 |         1 |          1 | D

现在,假设你不能做任何改变(阅读:修复)这个,你可以通过附加逗号和使用LIKE来捏造它:

select *
from STUDENT_ANSWERS a
join QUESTION_ANSWERS q on ',' + a.Answers + ',' like '%,' + q.Answer + ',%'
    and a.QuestionID = q.QuestionID

SQL Fiddle演示
请注意,* 假设您永远不会在QUESTION_ANSWERS.Answer* 中包含文本,。它也永远无法使用索引,所以它会比慢更慢。
如果您 * 绝对必须 * 在数据库中将其格式化为一行,则可以使用STUFFFOR XML PATH('')技巧来连接结果行。

bt1cpqcv

bt1cpqcv2#

这是只使用T-SQL语句的完整工作示例。我建议你创建一个单独的函数来分割CSV,并返回行集。此外,如果您正在处理大量数据,您可能需要创建一个CLR函数来拆分值。看看this article(这里有你需要的一切)。

DECLARE @StudentAnswers TABLE
(
     [AnswerID] INT
    ,[StudentID] INT
    ,[QuestionID] INT
    ,[Answers] VARCHAR(256)
);

DECLARE @QuestionAnswers TABLE
(
     [QuestionID] INT
    ,[Answer] CHAR
    ,[Text] VARCHAR(256)
);

INSERT INTO @StudentAnswers ([AnswerID], [StudentID], [QuestionID], [Answers])
VALUES (1, 1, 1, 'C,D')
      ,(2, 2, 1, 'A');

 INSERT INTO @QuestionAnswers ([QuestionID], [Answer], [Text])
 VALUES  (1, 'A', 'This is answer A')
        ,(1, 'B', 'B could also be correct')
        ,(1, 'C', 'Maybe it''s C?')
        ,(1, 'D', 'Definitely D!');

SELECT SA.[AnswerID]
      ,SA.[StudentID]
      ,SA.[QuestionID]
      ,T.c.value('.', 'CHAR')
      ,QA.[Text]
FROM @StudentAnswers SA
CROSS APPLY 
(
    SELECT CAST('<i>' + REPLACE([Answers], ',', '</i><i>') + '</i>' AS XML) Answers
) DS
CROSS APPLY DS.Answers.nodes('i') T(c)
INNER JOIN @QuestionAnswers QA
    ON SA.[QuestionID] = QA.[QuestionID]
    AND T.c.value('.', 'CHAR') = QA.[Answer];
cclgggtu

cclgggtu3#

试试这个

select answerid,studentid,a.QuestionID,group_concat(b.text) from student_answers a left join QUESTION_ANSWERS b on b.questionid= a.questionid and  FIND_IN_SET(b.Answer, a.Answers)
group by a.questionid

绝对有效。

mklgxw1f

mklgxw1f4#

我一直在问同样的问题。但仅适用于MariaDB。

select a.names, second,
  group_concat(second order by locate(concat('/', b.names, '/'), concat('/',a.names,'/')) asc)
from mytable a
join temp b on concat('/',a.names,'/') like concat('%/', b.names, '/%')
group by a.names

查看dbfiddle
那是:

变成:

如您所见,我们保存了一个名称顺序。
我希望它能帮助到别人!

相关问题