select a.classname from a where classname in (select classname from b where sec='女' and age=(select min(age) from b);
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班
用2楼的数据,查询可以这样写:SELECT T2.SCLASSNAME 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 = '女'
select * from a where (select max(sex)keep(dense_rank first order by age) from b where classname=a.classname)='女' 应该就可以了 其实有个问题,这个年龄如果是证书,是很容易重复的,如果年龄最小的有男生也有女生要怎么处理
--1. 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 = '女');
有两个表a和b,a储存着所有班级信息,b储存着所有学生id、age、sex和classname。 现在我要提取几个班级(该班级必须符合一个条件:班里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 * 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 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 = '女'
select * from test_student where (iclassid,istudentage) in( select t.iclassid, min(t.istudentage) from test_student t group by t.iclassid);
该班级必须符合一个条件:班里age最小的学生,sex=女这个条件其实不明确, 问题本身并不难。
select * from test_student where (iclassid,istudentage) in( select t.iclassid, min(t.istudentage) from test_student t where sStudentSex='女' group by t.iclassid);
select * from (select min(iStudentAge),iclassid,iStudentID from test_student where sStudentSex='女' group by iclassid,iStudentID)a, test_class b where a.iclassid=b.iclassid
select * from b where (age,classname) in(select min(age),classname from b group by classname) and sex = '女';
select a.classname,min(age),b.sex from b left join a on a.classid=b.classid where b.sex='女' group by a.classname,b.sex
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