问题描述

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语句

解决方案

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

 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

解决方案二：

这个给的条件不够啊，怎么叫女生成绩比男生成绩好？

解决方案三：

查询每门课里女生高于所有男生成绩的sql

 SELECT student.sid,
       student.sname,
       student.sex,
       sc.cid,
       sc.grade
  FROM student,
       sc,
       (  SELECT sc.cid, MAX (grade) grade
            FROM student, sc
           WHERE student.sid = sc.sid AND student.sex = 'm'
        GROUP BY sc.cid) A
 WHERE     student.sid = sc.sid
       AND sc.cid = A.cid
       AND STUDENT.SEX = 'f'
       AND sc.grade > A.grade