题目来源：https://blog.csdn.net/flycat296/article/details/63681089

teradata实现：

drop table student;create table student(s_id varchar(10),sname varchar(20),sage date,sex varchar(20));insert into Student values(‘01‘ , ‘赵雷‘ ,‘1990-01-01‘ , ‘男‘);insert into Student values(‘02‘ , ‘钱电‘ , ‘1990-12-21‘ , ‘男‘);insert into Student values(‘03‘ , ‘孙风‘ , ‘1990-05-20‘ , ‘男‘);insert into Student values(‘04‘ , ‘李云‘ , ‘1990-08-06‘ , ‘男‘);insert into Student values(‘05‘ , ‘周梅‘ , ‘1991-12-01‘ , ‘女‘);insert into Student values(‘06‘ , ‘吴兰‘ , ‘1992-03-01‘ , ‘女‘);insert into Student values(‘07‘ , ‘郑竹‘ , ‘1989-07-01‘ , ‘女‘);insert into Student values(‘08‘ , ‘王菊‘ , ‘1990-01-20‘ , ‘女‘);select * from student;create table course(c_id varchar(10),cname varchar(20),t_id varchar(10));insert into Course values(‘01‘ , ‘语文‘ , ‘02‘);insert into Course values(‘02‘ , ‘数学‘ , ‘01‘);insert into Course values(‘03‘ , ‘英语‘ , ‘03‘);select * from course;create table teacher(t_id varchar(10),tname varchar(20));insert into Teacher values(‘01‘ , ‘张三‘);insert into Teacher values(‘02‘ , ‘李四‘);insert into Teacher values(‘03‘ , ‘王五‘);select * from teacher;create table sc(s_id varchar(10),c_id varchar(10),score decimal(18,1));insert into SC values(‘01‘ , ‘01‘ , 80);insert into SC values(‘01‘ , ‘02‘ , 90);insert into SC values(‘01‘ , ‘03‘ , 99);insert into SC values(‘02‘ , ‘01‘ , 70);insert into SC values(‘02‘ , ‘02‘ , 60);insert into SC values(‘02‘ , ‘03‘ , 80);insert into SC values(‘03‘ , ‘01‘ , 80);insert into SC values(‘03‘ , ‘02‘ , 80);insert into SC values(‘03‘ , ‘03‘ , 80);insert into SC values(‘04‘ , ‘01‘ , 50);insert into SC values(‘04‘ , ‘02‘ , 30);insert into SC values(‘04‘ , ‘03‘ , 20);insert into SC values(‘05‘ , ‘01‘ , 76);insert into SC values(‘05‘ , ‘02‘ , 87);insert into SC values(‘06‘ , ‘01‘ , 31);insert into SC values(‘06‘ , ‘03‘ , 34);insert into SC values(‘07‘ , ‘02‘ , 89);insert into SC values(‘07‘ , ‘03‘ , 98);select * from sc order by s_id , c_id;/*1*/select t1.s_id,t1.c_id,t1.score,t2.c_id,t2.score from sc t1 inner join sc t2on t1.s_id=t2.s_id and t1.c_id=‘01‘and t2.c_id=‘02‘and t1.score>t2.score;select a.s_id,a.c_id,b.s_id,b.c_id from (select *from sc where c_id=‘01‘) aleft join (select * from sc where c_id=‘02‘) bon a.s_id=b.s_idwhere a.score >b.score;/*1.1*/select t1.s_id,t1.c_id,t2.s_id,t2.c_idfrom sc t1 inner join sc t2on t1.s_id=t2.s_id and t1.c_id=‘01‘and t2.c_id=‘02‘;select *from (select *from sc where c_id=‘01‘) a left join (select * from sc where c_id=‘02‘) bon a.s_id=b.s_id where b.s_id is not null;/*1.2*/select *from (select *from sc where c_id=‘01‘) a left join (select * from sc where c_id=‘02‘) bon a.s_id=b.s_id ;/*1.3*/select * from sc where c_id=‘02‘and s_id not in(select s_id from sc where c_id=‘01‘);/*2*/select a.s_id,b.sname,avg(a.score)from sc a left join student bon a.s_id=b.s_id group by a.s_id,b.snamehaving avg(a.score)>=60;/*3*/select * from student where s_id in (select s_id from sc group by s_id)/*4*/select a.s_id,a.sname,count(b.s_id),sum(b.score)from student a left join sc bon a.s_id=b.s_idgroup by a.s_id , a.sname;/*4.1*/select a.s_id,b.s_id, a.countclass,a.totlescorefrom(select s_id,count(s_id) countclass,sum(score) totlescore from sc group by s_id) aleft join student bon a.s_id=b.s_id /*5*/ select count(*) from teacher where tname like ‘李%‘; /*6*/select *from student a left join sc bon a.s_id=b.s_idleft join course con b.c_id= c.c_idleft join teacher don c.t_id =d.t_idwhere d.tname=‘张三‘;/*7*/select a.s_id,a.sname,a.sage,a.sex from student a left join sc b on a.s_id =b.s_id having count(b.s_id)<3group by a.s_id,a.sname,a.sage,a.sex ;/*8*/select * from student where s_id in (select distinct s_id from sc where c_id in (select c_id from sc where s_id=‘01‘) )/*9*/select * from student where s_id in(select s_id from sc where c_id in (select c_id from sc where s_id=‘01‘ ) and s_id<>‘01‘group by s_id having count(s_id)>=3) ;/*10*/select * from student where s_id not in (select s_id from sc where c_id in (select c_id from course where t_id in (select t_id from teacher where tname=‘张三‘)));/*11*/select a.s_id,a.sname, b.avg_scorefrom student a right join (select s_id ,avg(score) avg_score from sc where score<60 group by s_id having count(score)>=2) bon a.s_id =b.s_id; /*12*/select a.s_id,a.sname,a.sage,a.sex, b.score from student a right join sc b on a.s_id=b.s_id where b.c_id=‘01‘and b.score<60order by b.score desc;
/*13*难点：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩*/select s_id, max(case c_id when ‘01‘ then score else 0 end ) a,max(case c_id when ‘02‘ then score else 0 end ) b,max(case c_id when ‘03‘ then score else 0 end) c,avg(score)from scgroup by s_id order by 5 desc;/*14*/select a.c_id,a.cname,b.highest,b.lowest,b.avgscore,c.jigelv,d.middle,e.excellent,f.great,g.people_numberfrom course a left join(select c_id,max(score) highest, min(score) lowest ,avg(score) avgscorefrom sc group by c_id) bon a.c_id=b.c_idleft join (select c_id,(sum(case when score>=60 then 1 else 0 end)*1.00/count(*)*100) jigelv from sc group by c_id) con a.c_id=c.c_idleft join (select c_id ,(sum(case when score>=70 and score<80 then 1 else 0 end)*1.00/count(*)*100) middle from sc group by c_id) don a.c_id=d.c_idleft join(select c_id,(sum(case when score>=80 and score<90 then 1 else 0 end )*1.00/count(*)*100) excellent from sc group by c_id)eon a.c_id=e.c_idleft join(select c_id,(sum(case when score>=90 then 1 else 0 end )*1.00/count(*)*100) great from sc group by c_id ) fon a.c_id=f.c_idleft join (select c_id,count(*) people_number from sc group by c_id) gon a.c_id=g.c_idorder by g.people_number,a.c_id;/*15,15.1 row_number()over() rank()over() dense_rank()*/select s_id, c_id,score,row_number()over(partition by c_id order by score desc ) rank1from sc;select s_id,c_id,score,rank()over(partition by c_id order by score desc) rank1from sc;select s_id,c_id,score,dense_rank() over(partition by c_id order by score desc) rank1from sc;/*16*/select s_id,sum(score),rank()over(order by sum(score) desc) from sc group by s_id;
/*16.1*/select s_id,sum(score),dense_rank()over(order by sum(score) desc) from sc group by s_id;/*17*/select c_id, sum(case when score<=60 then 1 else 0 end ) a1, (sum(case when score<=60 then 1 else 0 end)*1.00/count(*) ) a2from sc group by c_idorder by c_id;/*18*/select * from (select c_id,s_id,score,rank()over( partition by c_id order by score desc ) rank1from sc ) b where b.rank1<=3/*方法二：难点*/select a.c_id,a.s_id ,a.score,b.score from sc aleft join sc b on a.c_id=b.c_id and a.score<b.scoregroup by a.s_id,a.c_id,a.scorehaving count(b.s_id)<3order by a.c_id,a.score desc;select * from sc a where (select count(*) from sc where c_id=a.c_id and score>a.score)<3order by a.c_id, a.score desc/*19*/select c_id ,count(*) from sc group by c_id;/*20*/select s_id from sc group by s_id having count(s_id)=2/*21*/select sex,count(sex) from student group by sex/*22*/select * from student where sname like‘%风%‘;/*23*/select a.s_id,b.countnumber from student aleft join (select sname,sex,count(*) countnumber from student group by sname,sex)bon a.sname=b.snameand a.sex=b.sexwhere b.countnumber>1;/*24 to_char()的使用*/select s_id ,sname from student where to_char(sage,‘yyyy‘)=1990/*25*/select c_id,avg(score) avgscore from sc group by c_id order by avgscore desc,c_id;/*26*/select a.s_id,a.sname,avg(b.score) avgscorefrom student a left join sc bon a.s_id=b.s_id group by a.s_id ,a.snamehaving avg(b.score)>85;/*27*/select a.sname,b.scorefrom student a left join sc bon a.s_id=b.s_idleft join course con b.c_id=c.c_id where c.cname=‘数学‘and b.score<60;/*28*/select a.s_id,a.sname,b.c_id,b.score,c.cnamefrom student a left join sc bon a.s_id=b.s_idleft join course con b.c_id=c.c_idorder by a.s_id,b.c_id;/*29%%*/select a.sname,c.cname,b.scorefrom (select s_id,c_id,score from sc where score>70) b left joincourse c on b.c_id=c.c_idleft join student aon b.s_id=a.s_id;/*30*/select c_id,count(*)from sc where score<60group by c_id;/*31*/select a.c_id,count(*) from course a left join sc bon a.c_id=b.c_idgroup by a.c_id
/*32*/select c_id,count(*) from sc group by c_id;
/*33 %%成绩不重复*/select top 1* from sc where c_id in (select c_id from course where t_id in (select t_id from teacher where tname=‘张三‘))order by score desc;/*34 %%成绩重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩*/select a.s_id,a.sname,b.score,b.c_id from (select c_id,max(score) maxscorefrom sc group by c_id ) e left join sc b on e.maxscore=b.score and e.c_id=b.c_id left join course c on b.c_id=c.c_id left join teacher d on c.t_id=d.t_id left join student a on a.s_id=b.s_id where d.tname=‘张三‘; /*dense_rank()*/ select e.s_id,e.c_id,e.score from (select s_id ,c_id, score,dense_rank()over(partition by c_id order by s_id)rank1 from sc) e left join course c on e.c_id=c.c_id left join teacher d on c.t_id=d.t_id where d.tname=‘张三‘ and e.rank1=1; select top 1* from (select s_id ,c_id, score,dense_rank()over(partition by c_id order by s_id)rank1 from sc) e left join course c on e.c_id=c.c_id left join teacher d on c.t_id=d.t_id where d.tname=‘张三‘ ; /*不同学生课程相同，分数相同*/ select a.s_id,a.c_id,a.score,b.s_id,b.c_id,b.score from sc a left join sc b on a.score=b.score and a.s_id>b.s_id and a.c_id=b.c_id where b.score is not null; /*35,查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩*/select c.s_id,max(c.c_id) c_id,max(c.score) score from sc cleft join (select s_id ,avg(score)a from sc group by s_id)bon c.s_id=b.s_idwhere c.score=b.agroup by c.s_idhaving count(0)=(select count(0) from sc where s_id=c.s_id) /*存在三行，如何归并成一行*/ select a.s_id,a.c_id,b.s_id,b.c_id,a.score,b.score from (select s_id,c_id, score,rank()over(partition by s_id order by score) rank1 from sc) a inner join (select s_id,c_id, score,rank()over(partition by s_id order by score) rank1 from sc)b on a.rank1=b.rank1 and a.s_id=b.s_id and a.c_id<b.c_id; /*36*/ select * from (select s_id,c_id,score,rank()over(partition by c_id order by score) rank1 from sc )a where a.rank1<3; /*37*/select c_id,count(*)from scgroup by c_idhaving count(*)>5; /*38*/ select s_id from sc group by s_id having count(c_id)>=2 /*39*/select s_id from scgroup by s_id having count(c_id)=3;
/*40*/select s_id,sname,extract(year from date)-extract(year from sage) age from student;