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

Question

select 
a.Sno,a.Sname,avg(b.Scgrade)avg_Scgrade
from 
s a
join
SC b on a.Sno=b.Sno
join
(select Sno,count(*) con from SC where Scgrade<60 group by Sno)c on a.Sno=c.Sno
group by a.Sno,a.Sname

Answer 1

select 
a.Sno,a.Sname,avg(b.Scgrade)avg_Scgrade
from 
s a
join
SC b on a.Sno=b.Sno
join
(select Sno,count(*) con from SC where Scgrade<60 group by Sno)c on a.Sno=c.Sno and c.con>=2--加上
group by a.Sno,a.Sname

Answer 2

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/*
SName      AVGScgrade
---------- ---------------------------------------
王五         68.200000
*/

Answer 3

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 

Answer 4

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 

Answer 5

--> 测试数据: @s
declare @s table (Sno int,Sname varchar(4))
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,'老子'
--> 测试数据: @C
declare @C table (Cno int,Cteacher varchar(4))
insert into @C
select 1,'嘟嘟' union all
select 2,'吱吱' union all
select 3,'李明'
--> 测试数据: @SC
declare @SC table (Sno int,Cno int,Scgrade numeric(3,1))
insert into @SC
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,68select sname,avg(Scgrade) from @s a,
(select sno,Scgrade,px=count(1) from @sc where Scgrade<60 group by sno,Scgrade)b
where a.sno=b.sno group by b.sno,a.sname having count(1)>1
--结果：
sname                                          
----- ---------------------------------------- 
王五    59.000000

Answer 6

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
*/