MS SQL SERVER 学生成绩问题（高手请进)

[yzgnick]

用一条SQL语句查询出二门以上（包含二门）不及格的学生姓名以及平均成绩，表内容如下：
S 学生表(Sno,Sname)
SC课程关系表(Sno,Cno,Scgrade)
C课程表(Cno,Cteacher)
s
Sno Sname
1 张三
2 李四
3 王五
4 赵六
5 孔子
6 老子

C
Cno Cteacher
1 嘟嘟
2 吱吱
3 李明


SC
Sno Cno Scgrade
1 1 89.5
1 2 78.6
1 3 46.5
2 1 98
2 2 78
3 1 58.5
3 2 86.5
3 3 59.5
4 1 78
5 1 68

[子陌红尘]

try:

select  
    a.Sno,a.Sname,avg(b.Scgrade) as [avg]
from 
    S a,SC b,C 
where 
    a.Sno=b.Sno and b.Cno=c.Cno
    and
    (select count(1) from SC where Sno=a.Sno and Scgrade<60)>1
group by
    a.Sno,a.Sname

[ws_hgo]

我不是高手
但是
我看看

[子陌红尘]

create table S (Sno int,Sname varchar(10)) 
create table SC(Sno int,Cno int,Scgrade numeric(5,1)) 
create table C (Cno int,Cteacher varchar(10)) 
go
insert into S values(1,'张三') 
insert into S values(2,'李四') 
insert into S values(3,'王五') 
insert into S values(4,'赵六') 
insert into S values(5,'孔子') 
insert into S values(6,'老子') 

insert into C values(1,'嘟嘟') 
insert into C values(2,'吱吱') 
insert into C values(3,'李明') 

insert into SC values(1,1,89.5) 
insert into SC values(1,2,78.6) 
insert into SC values(1,3,46.5) 
insert into SC values(2,1,98  )
insert into SC values(2,2,78  )
insert into SC values(3,1,58.5) 
insert into SC values(3,2,86.5) 
insert into SC values(3,3,59.5) 
insert into SC values(4,1,78  )
insert into SC values(5,1,68  ) 
go

select  
    a.Sno,a.Sname,avg(b.Scgrade) as [Avg]
from 
    S a,SC b,C 
where 
    a.Sno=b.Sno and b.Cno=c.Cno
    and
    (select count(1) from SC where Sno=a.Sno and Scgrade<60)>1
group by
    a.Sno,a.Sname
go

/*
Sno         Sname      Avg                                      
----------- ---------- ---------------------------------------- 
3           王五         68.166666
*/

drop table S,C,SC
go

[ljhcy99]

Select S.Sname, AVG. score
From S,
(Select top 2 with ties avg(Scgrade) As score, Sno
From SC
Where SC. Cno in(select Cno from C)
Group by Sno
Having score<60
)As AVG

Where S.Sno=AVG.Sno

[RPFly]

Select S.Sname,avg(Scgrade)
From S
Join SC On S.Sno=SC.sno
where Scgrade <60
Group By S.Sname
Having count(1)>1

[Garnett_KG]

DECLARE @s TABLE(SNo INT,Sname varchar(10))
INSERT INTO @s SELECT 
1 , '张三' UNION ALL SELECT
2 , '李四' UNION ALL SELECT
3 , '王五' UNION ALL SELECT
4 , '赵六' UNION ALL SELECT
5 , '孔子' UNION ALL SELECT
6 , '老子' 

DECLARE @C TABLE (Cno INT, Cteacher VARCHAR(10))
INSERT INTO @c SELECT 
1 , '嘟嘟' UNION ALL SELECT 
2 , '吱吱' UNION ALL SELECT
3 , '李明' 


DECLARE @SC  TABLE (SNo INT,Cno INT,Scgrade decimal(15,1))
INSERT INTO @Sc SELECT 
1   , 1 , 89.5  UNION ALL SELECT
1   , 1 , 89.5  UNION ALL SELECT
1   , 1 , 89.5  UNION ALL SELECT
1   , 2 , 78.6 UNION ALL SELECT
1   , 3 , 46.5 UNION ALL SELECT
2   , 1 , 98 UNION ALL SELECT
2   , 2 , 78 UNION ALL SELECT
3   , 1 , 58.5 UNION ALL SELECT
3   , 2 , 86.5 UNION ALL SELECT
3   , 3 , 59.5 UNION ALL SELECT
4   , 1 , 78 UNION ALL SELECT
5   ,1  ,68 

SELECT SName,AVGScgrade
FROM @s s INNER JOIN 
	(
	SELECT SC.SNo,ROUND(AVG(Scgrade),1) as AVGScgrade
	FROM @c c LEFT JOIN @SC SC
			ON C.CNo=SC.CNo
	GROUP BY SC.SNo
	HAVING SUM(CASE WHEN ISNULL(sc.Scgrade,0)<60 THEN 1 ELSE 0 END)>=2
	) b ON s.SNo=b.SNo

[律己修心]

select Sname avg(Scgrade) as Scgrade 
from S inner join SC on S.Sno=SC.Sno
where Sno in (select Sno from SC where Scgrade <60 group by Sno having count(1)>1)
group by Sname
order by Sname

[律己修心]

这个已经测试过

select Sname,cast(avg(Scgrade) as numeric(5,2)) as Scgrade 
from S inner join SC on S.Sno=SC.Sno
where S.Sno in (select Sno from SC where Scgrade <60 group by Sno having count(1)>1)
group by Sname
order by Sname

[律己修心]

select Sname,cast(avg(Scgrade) as numeric(5,2)) as Scgrade from S,SC
where S.Sno=SC.Sno and (select count(1) from SC where Sno=S.Sno and Scgrade<60)>1
group by Sname order by Sname

[时光瞄]

select sname,avg(grade) from S a,C b where a.sno=b.sno and (select count(*) from C where sno=b.sno and 60>b.grade)>=2 
 group by sname

[时光瞄]

晕，代码贴漏了

select sname,avg(grade) from S a,C b where a.sno=b.sno and (select count(*) from C where sno=b.sno and 60>b.grade)>=2 
 group by sname

[等不到来世]

create table S (Sno int,Sname varchar(10)) 
create table SC(Sno int,Cno int,Scgrade numeric(5,1))
go
insert into S values(1,'张三') 
insert into S values(2,'李四') 
insert into S values(3,'王五') 
insert into S values(4,'赵六') 
insert into S values(5,'孔子') 
insert into S values(6,'老子') 

insert into SC values(1,1,89.5) 
insert into SC values(1,2,78.6) 
insert into SC values(1,3,46.5) 
insert into SC values(2,1,98  )
insert into SC values(2,2,78  )
insert into SC values(3,1,58.5) 
insert into SC values(3,2,86.5) 
insert into SC values(3,3,59.5) 
insert into SC values(4,1,78  )
insert into SC values(5,1,68  ) 
go


select S.Sname,cast(b.avggrade as decimal(10,1))
from S join (select Sno from SC where Scgrade<60 group by Sno having count(*)>1) a on S.Sno=a.Sno
join (select Sno,avggrade=avg(Scgrade) from SC group by Sno) b on b.Sno=a.Sno

/*
----------
王五	68.2
*/

drop table S,C,SC

[wxg22526451]

select  a.Sname,
	avgScgrade=(select AVG(Scgrade) from @SC where Sno=a.Sno)
from  @s a 
join  @SC b 
on a.Sno=b.Sno and b.Scgrade<60 
group by a.Sno,Sname having count(*)>=2 
/*
Sname avgScgrade
----- ---------------------------------------
王五    68.166666
*/