求助一个SQL语句怎么写

[舞步灬]

如图一个表的数据结构。
如何查询所有三门课程都及格的学生信息？这个SQL语句该如何写？
谢谢帮助的大神！

[绿豆丫]

create table #t (name varchar(20),kc nvarchar(20) ,score int)

insert into  #t values('zs','english',40)
insert into  #t values('zs','chinese',60)
insert into  #t values('zs','japan',80)


insert into  #t values('ww','english',60)
insert into  #t values('ww','chinese',60)
insert into  #t values('ww','japan',80)

insert into  #t values('zl','english',40)
insert into  #t values('zl','chinese',60)
insert into  #t values('zl','japan',80)



select name from #t a where exists(select * from #t where score>=60 and a.name=name and a.kc=kc) group by name having count(1)>=3

[道玄希言]

;with tb01(id, name, class, score) as 
(
    select 1, '张三','英语', 60 union all
    select 2, '张三','语文', 50 union all
    select 3, '张三','数学', 70 union all
    select 4, '李四','英语', 80 union all
    select 5, '李四','语文', 70 union all
    select 6, '李四','数学', 90 union all
    select 7, '王五','英语', 60 union all
    select 8, '赵六','语文', 40 union all
    select 9, '孙七','数学', 90 
)

select name, score as minscore from 
(
select min(score) as score, count(1) as num, name from tb01 group by name
) t
where t.num = 3 and t.score >=60   --三门课程全部都有成绩，且最低成绩不低于 60 分。