有两个表a和b,a储存着所有班级信息,b储存着所有学生id、age、sex和classname。
现在我要提取几个班级(该班级必须符合一个条件:班里age最小的学生,sex=女)。请问sql该如何实现?
现在我要提取几个班级(该班级必须符合一个条件:班里age最小的学生,sex=女)。请问sql该如何实现?
解决方案 »
- 为什么NOARCHIVELOG模式做recover后,为什么会提示数据文件块损坏呢?
- 以表的形式exp/imp能否包括包关联的所有对象
- 急!! 下面的sql语句怎么理解,那位高手能帮忙解释一下。谢谢!!!
- 用javascript生成了一棵树,如何删除上级节点的时候把下级所有节点的内容全部删除
- oracle 新手请教 符合下面条件的触发器该如何编写
- 数据量太大,磁盘空间不足问题
- oracle多表查询筛选最新记录
- 重复记录筛选 高手赐教 在线等
- 求一简单树形结构的运算结果,请熟悉的朋友指点一下
- 实在搞不定啊!!各位大神请帮忙看看!!!
- oracle 存储过程中的多表查询问题? 急 在线等
- 业务中的一个问题,请教大侠帮助!
drop table Test_Class
/create table Test_Class(
iClassID Integer,
sClassName varchar2(30)
)
/drop table Test_Student
/create table Test_Student(
iStudentID Integer,
iClassID Integer,
sStudentName varchar2(50),
iStudentAge Integer,
sStudentSex varchar2(2)
)
/insert into Test_Class(iClassID, sClassName)
select 1, '高120班' from dual
union all
select 2, '高121班' from dual
union all
select 3, '高122班' from dual
union all
select 4, '高123班' from dual
union all
select 5, '高124班' from dual;insert into Test_Student(iStudentID, iClassID, sStudentName, iStudentAge, sStudentSex)
select 1001, 1, '赵男', 16, '男' from dual
union all
select 1002, 1, '钱女', 17, '女' from dual
union all
select 2001, 2, '孙女', 16, '女' from dual
union all
select 2001, 2, '李男', 17, '男' from dual
union all
select 3001, 3, '周男', 16, '男' from dual
union all
select 3001, 3, '武女', 17, '女' from dual
union all
select 4001, 4, '郑男', 16, '男' from dual
union all
select 4001, 4, '王女', 20, '女' from dual
union all
select 5001, 5, '冯男', 17, '男' from dual
union all
select 5001, 5, '陈女', 15, '女' from dual;Commit;-- 先找班级年龄最小同学的性别
select Distinct iClassID, FIRST_VALUE(sStudentSex) over (PARTITION BY iClassID ORDER BY iStudentAge ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) As sStudentSex
from Test_Student;-- 查询语句
select Test_Class.iClassID, Test_Class.sClassName
from Test_Class, (select Distinct iClassID, FIRST_VALUE(sStudentSex) over (PARTITION BY iClassID ORDER BY iStudentAge ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) As sStudentSex
from Test_Student) TempStudent
where Test_Class.iClassID = TempStudent.iClassID
and TempStudent.sStudentSex = '女';
-- 结果
ICLASSID SCLASSNAME
--------------------------------------- ------------------------------
2 高121班
5 高124班
FROM (SELECT T.*,
RANK() OVER(PARTITION BY ICLASSID ORDER BY ISTUDENTAGE) RN
FROM TEST_STUDENT T) T1,
Test_Class T2
WHERE T1.ICLASSID = T2.ICLASSID
AND T1.RN = 1
AND T1.SSTUDENTSEX = '女'
where (select max(sex)keep(dense_rank first order by age) from b
where classname=a.classname)='女'
应该就可以了
其实有个问题,这个年龄如果是证书,是很容易重复的,如果年龄最小的有男生也有女生要怎么处理
SELECT *
FROM a
WHERE a.classname IN
(SELECT classname
FROM b
WHERE b.sex = '女'
AND EXISTS (SELECT 1
FROM (SELECT b.classname, MIN(age) age
FROM b
GROUP BY b.classname) c
WHERE c.classname = b.classname
AND c.age = b.age))
--2.
SELECT *
FROM a
WHERE a.classname IN (SELECT classname
FROM (SELECT b.*,
row_number() over(PARTITION BY b.classname ORDER BY age ASC) rn
FROM b)
WHERE rn = 1
AND sex = '女');
现在我要提取几个班级(该班级必须符合一个条件:班里age最小的学生,sex=女)。
select * from a where exits(select 1 from
(select id,age,sex,classname,row_number() over(partition by classname order by age asc ) rn from b) t where a.classname=classname and rn=1)
(select id,age,sex,classname,row_number() over(partition by classname order by age asc ) rn from b where sex='女') t where a.classname=classname and rn=1)
select a.* , b.*
from a , b t
where a.classname = t.classname and t.sex='女' and t.age = (select min(age) from b where sex='女' and classname = t.classname)select a.* , b.*
from a , b t
where a.classname = t.classname and t.sex='女' and not exists (select 1 from b where sex='女' and classname = t.classname and age < t.age)
-- Create table
create table STUDENT
(
STU_ID NUMBER,
AGE NUMBER,
SEX CHAR(2),
C_ID NUMBER
);-- Create table
create table CLASS
(
CLASS_NAME VARCHAR2(20),
CID NUMBER
); select c.class_name,stu.sex, stu.age, stu.c_id
from student stu,
(select s.c_id, min(s.age) as age
from student s
group by s.c_id
order by s.c_id asc) kk,
class c
where stu.c_id = kk.c_id
and stu.age = kk.age
and c.cid = stu.c_id
and stu.sex = '女'
a是班级信息
b是学生信息应该还有一张表,表示 a和b的关系c表吧其实你的问题 应该是在表c中查处 每个班的最小女生是谁这问大家可能很快就能回答上。
select * from test_student
where (iclassid,istudentage) in(
select t.iclassid, min(t.istudentage)
from test_student t
group by t.iclassid);
where (iclassid,istudentage) in(
select t.iclassid, min(t.istudentage)
from test_student t
where sStudentSex='女'
group by t.iclassid);
group by iclassid,iStudentID)a, test_class b where a.iclassid=b.iclassid
group by a.classname,b.sex