sql - SQL查询从两个表中获取MCQ问题和答案的数据

Question

MCQ 问答表名 = Table1

每个MCQ问题和选项代码表的选项名称 = 表2

表格1

表2

SQL查询获取数据形式为-</p>

问题，所有 3 个选项和正确选项

标签： sqldatabase

Answer 1

我假设您使用 MSSQL 作为数据库

采用JOINS

小提琴演示

SELECT Q.ID, Q.Que Quetion, A.Choice Options, Op.Choice AS Answer
FROM Table1 Q JOIN Table2 A ON Q.Id= A.Id
LEFT JOIN Table2 Op ON Q.ID = Op.Id AND Q.Ans_Id = Op.Choice_Id

或者

SELECT Q.ID, Q.Que Quetion, A.Choice Options, Op.Choice AS Answer
FROM Table1 Q JOIN Table2 A ON Q.Id= A.Id
LEFT JOIN Table2 Op ON Q.ID = Op.Id AND Q.Ans_Id = Op.Choice_Id 
                   AND Op.Choice_Id = A.Choice_Id 

输出

ID  Quetion             Options      Answer
1   Sample Question #1  (a) sasas   (a) sasas
1   Sample Question #1  (b) saewew  (a) sasas
1   Sample Question #1  (c) 4234    (a) sasas
2   Sample Question #2  (a) xxxxx   (c) sd4234
2   Sample Question #2  (b) 5855sz  (c) sd4234
2   Sample Question #2  (c) sd4234  (c) sd4234
3   Sample Question #3  (a) bbbbbb  (b) saew34
3   Sample Question #3  (b) saew34  (b) saew34
3   Sample Question #3  (c) 42d34   (b) saew34