select b.stuID,b.classID,b.stuname,max(b.age),a.classname from student b left join class a on a.classID=b.classID group by b.stuID,b.classid,b.stuname,a.classname order by b.age
select top 2 *, b.stuID,b.classID,b.stuname,max(b.age),a.classname from student b left join class a on a.classID=b.classID group by b.stuID,b.classid,b.stuname,a.classname order by b.age desc
---ROW_NUMBER()函 create table #tb1(classID int,classname varchar(10)) go insert into #tb1 select 1,'a' insert into #tb1 select 2,'b' insert into #tb1 select 3,'c' create table #tb2(stuID int,classID int,stuname varchar(10),age int) go insert into #tb2 select 1,1,'s1',10 insert into #tb2 select 2,1,'s2',11 insert into #tb2 select 3,1,'s3',12insert into #tb2 select 1,2,'s1',10 insert into #tb2 select 2,2,'s2',11 insert into #tb2 select 3,2,'s3',12insert into #tb2 select 1,3,'s1',10 insert into #tb2 select 2,3,'s2',11 insert into #tb2 select 3,3,'s3',12 ------------- select * from ( select row_number()over(partition by t2.classname order by t2.classname,age desc ) rn, t2.classname,t1.stuname,age from #tb2 t1 left join #tb1 t2 on t1.classID=t2.classID ) t3 where t3.rn<3 ---------- rn classname stuname age 1 a s3 12 2 a s2 11 1 b s3 12 2 b s2 11 1 c s3 12 2 c s2 11
select m.* , n.classID ,n.stuname,n.age from clsssid m, ( select s.* , row_number() over(partition by classid order by age desc) px from stuid s) n where m.classid = n.classid and px <= 2 order by m.classid , n.age desc
--来个最容易理解的 with class as ( select 1 cid, '1ban' cname from dual union all select 2 cid, '2ban' cname from dual union all select 3 cid, '2ban' cname from dual ), studentas ( select 1 cid, 1 sid, 'AA' sname, 100 age from dual union all select 1 cid, 2 sid, 'AB' sname, 150 age from dual union all select 1 cid, 3 sid, 'AC' sname, 200 age from dual union all select 2 cid, 4 sid, 'BA' sname, 250 age from dual union all select 2 cid, 5 sid, 'BB' sname, 300 age from dual union all select 2 cid, 6 sid, 'BC' sname, 350 age from dual union all select 3 cid, 7 sid, 'CA' sname, 400 age from dual union all select 3 cid, 7 sid, 'CB' sname, 450 age from dual union all select 3 cid, 7 sid, 'CC' sname, 500 age from dual ) select * from (select sid, sname, age from class, student where class.cid = student.cid and class.cid=1 order by age desc) where rownum <3 union all select * from (select sid, sname, age from class, student where class.cid = student.cid and class.cid=2 order by age desc) where rownum <3 union all select * from (select sid, sname, age from class, student where class.cid = student.cid and class.cid=3 order by age desc) where rownum <3 -------------------------------------------------------- sid sname age 3 AC 200 2 AB 150 6 BC 350 5 BB 300 9 CC 500 8 CB 450
left join class a on a.classID=b.classID
group by b.stuID,b.classid,b.stuname,a.classname
order by b.age
left join class a on a.classID=b.classID
group by b.stuID,b.classid,b.stuname,a.classname
order by b.age desc
create table #tb1(classID int,classname varchar(10))
go
insert into #tb1 select 1,'a'
insert into #tb1 select 2,'b'
insert into #tb1 select 3,'c'
create table #tb2(stuID int,classID int,stuname varchar(10),age int)
go
insert into #tb2 select 1,1,'s1',10
insert into #tb2 select 2,1,'s2',11
insert into #tb2 select 3,1,'s3',12insert into #tb2 select 1,2,'s1',10
insert into #tb2 select 2,2,'s2',11
insert into #tb2 select 3,2,'s3',12insert into #tb2 select 1,3,'s1',10
insert into #tb2 select 2,3,'s2',11
insert into #tb2 select 3,3,'s3',12
-------------
select * from (
select row_number()over(partition by t2.classname order by t2.classname,age desc ) rn,
t2.classname,t1.stuname,age
from #tb2 t1
left join #tb1 t2
on t1.classID=t2.classID
) t3
where t3.rn<3
----------
rn classname stuname age
1 a s3 12
2 a s2 11
1 b s3 12
2 b s2 11
1 c s3 12
2 c s2 11
select m.* , n.classID ,n.stuname,n.age from clsssid m,
(
select s.* , row_number() over(partition by classid order by age desc) px from stuid s) n
where m.classid = n.classid and px <= 2
order by m.classid , n.age desc
--来个最容易理解的
with class as (
select 1 cid, '1ban' cname from dual union all
select 2 cid, '2ban' cname from dual union all
select 3 cid, '2ban' cname from dual
),
studentas (
select 1 cid, 1 sid, 'AA' sname, 100 age from dual union all
select 1 cid, 2 sid, 'AB' sname, 150 age from dual union all
select 1 cid, 3 sid, 'AC' sname, 200 age from dual union all
select 2 cid, 4 sid, 'BA' sname, 250 age from dual union all
select 2 cid, 5 sid, 'BB' sname, 300 age from dual union all
select 2 cid, 6 sid, 'BC' sname, 350 age from dual union all
select 3 cid, 7 sid, 'CA' sname, 400 age from dual union all
select 3 cid, 7 sid, 'CB' sname, 450 age from dual union all
select 3 cid, 7 sid, 'CC' sname, 500 age from dual
)
select * from (select sid, sname, age from class, student where class.cid = student.cid and
class.cid=1 order by age desc) where rownum <3
union all
select * from (select sid, sname, age from class, student where class.cid = student.cid and
class.cid=2 order by age desc) where rownum <3
union all
select * from (select sid, sname, age from class, student where class.cid = student.cid and
class.cid=3 order by age desc) where rownum <3
--------------------------------------------------------
sid sname age
3 AC 200
2 AB 150
6 BC 350
5 BB 300
9 CC 500
8 CB 450