SQL面试题

create table student_test(
sid int, 学生ID
tid int, 试题ID
score float,得分
flag int,是否正确
stid int 试卷ID
)
学生表
create table student(
id int, //学生id
name varchar(20) //学生姓名
)
试卷表
create table test(
id int,//试卷ID
name varchar(20) //试卷名称
)

1、

select s.name,t.name,sum( case when st.flag=1 then score else 0 end) score from student s inner join student_test st on s.id=st.sid inner join test t
on t.id=st.stid
group by s.name,t.name
order by score

2、
select s.name,t.name, count(st.flag)flag from student s inner join student_test st on s.id=st.sid inner join test t
on t.id=st.stid
where st.flag=0
group by s.name,t.name
union
select s.name,t.name, 0 flag from student s inner join student_test st on s.id=st.sid inner join test t
on t.id=st.stid
where st.flag=1
group by s.name,t.name
having count(st.flag)=(select count(tid) from student_test where stid=1 and sid=1);

有不足的地方欢迎指摘