查询学生成绩五分之一以上科目都及格的SQL,怎么写?(及格sorce>60分)

Question

table1: student(long sid,varchar nane)主键：sid
table2: cource(long cid,long sid,int score)主键：cid,sid查询学生成绩五分之一以上科目都及格的SQL,怎么写?(及格sorce>60分)

Answer 1

--创建测试数据表
create table cource
(
  cid int,
  sid int,
  score int
)
create table student
(
  sid int identity(1,1),
  [name] varchar(100)
)
create table class
(
  cid int identity(1,1),
  [name] varchar(100)
)--测试数据
insert into student (sid,[name])values ('张三') 
insert into student (sid,[name])values ('李四') 
insert into student (sid,[name])values ('王五') insert into class (cid,[name])values ('语文') 
insert into class (cid,[name])values ('数学') 
insert into class (cid,[name])values ('外语') 
insert into class (cid,[name])values ('物理') 
insert into class (cid,[name])values ('化学') 
insert into class (cid,[name])values ('生物') insert into cource (cid,sid,score)values (1,3,20) 
insert into cource (cid,sid,score)values (2,3,50) 
insert into cource (cid,sid,score)values (3,3,30) 
insert into cource (cid,sid,score)values (4,3,30) 
insert into cource (cid,sid,score)values (5,3,10) 
--省略部分成绩数据插入--检索
select b.sid,b.[name] from cource a left join student b
on a.sid=b.sid
where a.score>60
group by b.sid,b.[name]
having count(a.cid)>(select count(*)/5 from class)--结果sid     name
2 李四
1 张三

Answer 2

declare @t table(sName varchar(10),ctype varchar(10),score int)
insert into @t 
select 'a','语文',50 union all
select 'a','数学',70 union all
select 'a','英文',80 union all
select 'a','语文1',60 union all
select 'a','数学1',70 union all
select 'a','英文1',80 union all
select 'b','语文',50 union all
select 'b','数学',50 union all
select 'b','英文',50 union all
select 'b','语文1',50 union all
select 'b','数学1',50 union all
select 'b','英文1',80 
select distinct sname from @t a where not exists(select 1 from(
select *, rn=ntile(5) over(partition by sName order by score desc) from @t
)t where rn=1 and score<60 and sname=a.sname)

Answer 3

create table cource
(
  cid int,
  sid int,
  score int
)
create table student
(
  sid int identity(1,1),
  [name] varchar(100)
)
create table class
(
  cid int identity(1,1),
  [name] varchar(100)
)--测试数据
insert into student ([name])values ('张三') 
insert into student ([name])values ('李四') 
insert into student ([name])values ('王五') insert into class ([name])values ('语文') 
insert into class ([name])values ('数学') 
insert into class ([name])values ('外语') 
insert into class ([name])values ('物理') 
insert into class ([name])values ('化学') 
insert into class ([name])values ('生物') insert into cource (cid,sid,score)values (1,3,20) 
insert into cource (cid,sid,score)values (2,3,50) 
insert into cource (cid,sid,score)values (3,3,30) 
insert into cource (cid,sid,score)values (4,3,30) 
insert into cource (cid,sid,score)values (5,3,10) 
insert into cource (cid,sid,score)values (1,2,20) 
insert into cource (cid,sid,score)values (1,4,70) 
insert into cource (cid,sid,score)values (2,2,20) 
insert into cource (cid,sid,score)values (3,2,70) select a.sid,a.name,sum(case when c.score>60 then 1 else 0 end),count(c.score)
 from student a join cource c
  on a.sid=c.sid
  group by a.sid,a.name
  having sum(case when c.score>60 then 1 else 0 end)/cast(count(c.score)as numeric)>0.2 

Answer 4

借JJ的测试数据:
declare @t table(sName varchar(10),ctype varchar(10),score int)
insert into @t 
select 'a','语文',50 union all
select 'a','数学',70 union all
select 'a','英文',80 union all
select 'a','语文1',60 union all
select 'a','数学1',70 union all
select 'a','英文1',80 union all
select 'b','语文',50 union all
select 'b','数学',50 union all
select 'b','英文',50 union all
select 'b','语文1',50 union all
select 'b','数学1',50 union all
select 'b','英文1',80 select distinct sName 
from @t t
where not exists(
select 1
from @t
where sName=t.sName
group by sName
having sum(case when score<60 then 1 else 0 end)*1./count(1)>0.2)/*
sName
----------
a(1 行受影响)
*/

Answer 5

是我刚作的IBM笔试题,就两个表student与course．
查询学生成绩五分之一以上科目都及格的学生ＮＡＭＥ；
原题是英文，鄙人没翻译好，大家见谅．

Answer 6

--我也抄剪子哥的数据
declare @t table(sName varchar(10),ctype varchar(10),score int)
insert into @t 
select 'a','语文',50 union all
select 'a','数学',70 union all
select 'a','英文',80 union all
select 'a','语文1',60 union all
select 'a','数学1',70 union all
select 'a','英文1',80 union all
select 'b','语文',50 union all
select 'b','数学',50 union all
select 'b','英文',50 union all
select 'b','语文1',50 union all
select 'b','数学1',50 union all
select 'b','英文1',80 SELECT SNAME,SUM(CASE WHEN SCORE>=60 THEN 1 ELSE 0 END) 'OK',COUNT(1) 'ALL' 
,SUM(CASE WHEN SCORE>=60 THEN 1 ELSE 0 END)*1.00/COUNT(1) '%'
FROM @T 
GROUP BY SNAME
HAVING SUM(CASE WHEN SCORE>=60 THEN 1 ELSE 0 END)*1.00/COUNT(1)>=(1.00/5)--a 5 6 0.8333333333333

Answer 7

create table cource
(
  cid int,
  sid int,
  score int
)
create table student
(
  sid int identity(1,1),
  [name] varchar(100)
)
create table class
(
  cid int identity(1,1),
  [name] varchar(100)
)--测试数据
insert into student ([name])values ('张三') 
insert into student ([name])values ('李四') 
insert into student ([name])values ('王五') insert into class ([name])values ('语文') 
insert into class ([name])values ('数学') 
insert into class ([name])values ('外语') 
insert into class ([name])values ('物理') 
insert into class ([name])values ('化学') 
insert into class ([name])values ('生物') 
insert into cource (cid,sid,score)values (1,3,20) 
insert into cource (cid,sid,score)values (2,3,50) 
insert into cource (cid,sid,score)values (3,3,30) 
insert into cource (cid,sid,score)values (4,3,30) 
insert into cource (cid,sid,score)values (5,3,10) 
insert into cource (cid,sid,score)values (1,2,20) 
insert into cource (cid,sid,score)values (1,4,70) 
insert into cource (cid,sid,score)values (2,2,20) 
insert into cource (cid,sid,score)values (3,2,70)  
select * from student a,class b,cource c where a.sid = c.sid and b.cid = c.cidselect a.sid ,a.name from student a left join cource c on a.sid =c.sid left join class b on c.cid = b.cid and c.score> 60
group by a.sid ,a.name 
 having(count(c.cid)>(select 0.2*count(*) from cource where cource.sid = a.sid ))sid         name
----------- ----------------------------------------------------------------------------------------------------
2           李四
3           王五

Answer 8

这个就可以了，先MARK，然后自己再写一下看看