2,497
社区成员
发帖
与我相关
我的任务
分享
select A表.题号, case when A表.正确答案 = B表.用户输入答案 then C表.分值 else 0 end 分值
from A表
left join B表 on A表.题号 = B表.题号
left join C表 on A表.题号 = C表.题号
--题目ID, 正确答案
Create Table A(ID int, Answer varchar(50));
insert into A select 1, 'a';
insert into A select 2, 'a';
insert into A select 3, 'a';
--题目ID, 用户ID, 用户答案
Create Table B(ID int, UserID int, Answer varchar(50));
insert into B select 1, 1, 'a';
insert into B select 2, 1, 'b';
insert into B select 3, 1, 'a';
insert into B select 1, 2, 'a';
insert into B select 2, 2, 'a';
insert into B select 3, 2, 'a';
--题目ID, 分值
Create Table C(ID int, Point int);
insert into C select 1, 1;
insert into C select 2, 2;
insert into C select 3, 3;
--用户1
select b.userid, sum(c.point) as point
from b
left join a on b.ID = a.id and b.answer = a.answer
left join c on a.ID = c.id
where b.userid = 1
group by b.userid;
--用户2
select b.userid, sum(c.point) as point
from b
left join a on b.ID = a.id and b.answer = a.answer
left join c on a.ID = c.id
where b.userid = 2
group by b.userid;