34,590
社区成员
发帖
与我相关
我的任务
分享
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
*/
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
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
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,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 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
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
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
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