a451469487 2015-05-18 15:05 采纳率: 0%
浏览 2299

SQL测试题:查询每门课,女生成绩比男生成绩好的学生

--SQL测试题:查询每门课,女生成绩比男生成绩好的学生
--sid 学号
--sname 学生姓名
--sex 性别 f女 m男
--cid 课程编码
--grade 成绩
create table student(sid varchar(100), sname varchar(100), sex varchar(1));
create table sc(sid varchar(100), cid varchar(100), grade int);

insert into student(sid, sname, sex) values ('f1', '', 'f');
insert into student(sid, sname, sex) values ('f2', '', 'f');
insert into student(sid, sname, sex) values ('f3', '', 'f');
insert into student(sid, sname, sex) values ('f4', '', 'f');
insert into student(sid, sname, sex) values ('f5', '', 'f');
insert into student(sid, sname, sex) values ('f6', '', 'f');
insert into student(sid, sname, sex) values ('f7', '', 'f');

insert into student(sid, sname, sex) values ('m1', '', 'm');
insert into student(sid, sname, sex) values ('m2', '', 'm');
insert into student(sid, sname, sex) values ('m3', '', 'm');
insert into student(sid, sname, sex) values ('m4', '', 'm');
insert into student(sid, sname, sex) values ('m5', '', 'm');
insert into student(sid, sname, sex) values ('m6', '', 'm');
insert into student(sid, sname, sex) values ('m7', '', 'm');

insert into sc(sid, cid, grade) values ('f1', 'c1', 100);
insert into sc(sid, cid, grade) values ('f2', 'c1', 90);
insert into sc(sid, cid, grade) values ('f3', 'c1', 89);
insert into sc(sid, cid, grade) values ('f4', 'c1', 70);
insert into sc(sid, cid, grade) values ('f5', 'c1', 60);
insert into sc(sid, cid, grade) values ('f6', 'c1', 50);
insert into sc(sid, cid, grade) values ('f7', 'c1', 40);

insert into sc(sid, cid, grade) values ('m1', 'c1', 99);
insert into sc(sid, cid, grade) values ('m2', 'c1', 89);
insert into sc(sid, cid, grade) values ('m3', 'c1', 79);
insert into sc(sid, cid, grade) values ('m4', 'c1', 69);
insert into sc(sid, cid, grade) values ('m5', 'c1', 59);
insert into sc(sid, cid, grade) values ('m6', 'c1', 49);
insert into sc(sid, cid, grade) values ('m7', 'c1', 39);

insert into sc(sid, cid, grade) values ('f1', 'c2', 100);
insert into sc(sid, cid, grade) values ('f2', 'c2', 90);
insert into sc(sid, cid, grade) values ('f3', 'c2', 89);
insert into sc(sid, cid, grade) values ('f4', 'c2', 70);
insert into sc(sid, cid, grade) values ('f5', 'c2', 60);
insert into sc(sid, cid, grade) values ('f6', 'c2', 50);
insert into sc(sid, cid, grade) values ('f7', 'c2', 40);

insert into sc(sid, cid, grade) values ('m1', 'c2', 99);
insert into sc(sid, cid, grade) values ('m2', 'c2', 89);
insert into sc(sid, cid, grade) values ('m3', 'c2', 79);
insert into sc(sid, cid, grade) values ('m4', 'c2', 69);
insert into sc(sid, cid, grade) values ('m5', 'c2', 59);
insert into sc(sid, cid, grade) values ('m6', 'c2', 49);
insert into sc(sid, cid, grade) values ('m7', 'c2', 39);

求简单SQL语句

  • 写回答

3条回答 默认 最新

  • danielinbiti 2015-05-18 15:22
    关注

    你这成绩好,好没标准,下面是同一门课女生至少比一个男生成绩好

     select distinct sid,sname,sex from(
    select student.sid,student.sname,student.sex
    ,(select count(*) from sc,student where student.sid=sc.sid and student.sex='f' and grade<t.grade and sc.cid=t.cid) allnum 
    from student,sc t
    where student.sid=t.sid and student.sex='m'
    ) where allnum>0
    
    评论

报告相同问题?

悬赏问题

  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗