表结构:
MS SQL
Create table #temp
(
ID int,
name varchar(20),
age int,
cid int
)Oracle
Create global temporary table temp
(
ID int,
name varchar(20),
age int,
cid int
)
查询出每个班级(cid)年龄最小的前两位同学信息
MS SQL:select * from #temp a
where id in
(
select top 2 id from #temp
where a.cid=cid
order by age asc
)
没问题
Oracle:select *
from scott.student a
where sid in
(
select sid
from
(
select sid
from scott.student
where a.cid=cid
order by age asc
) b
where rownum<=2
)
只能得到所有记录中年龄最小的两条记录为什么??
MS SQL
Create table #temp
(
ID int,
name varchar(20),
age int,
cid int
)Oracle
Create global temporary table temp
(
ID int,
name varchar(20),
age int,
cid int
)
查询出每个班级(cid)年龄最小的前两位同学信息
MS SQL:select * from #temp a
where id in
(
select top 2 id from #temp
where a.cid=cid
order by age asc
)
没问题
Oracle:select *
from scott.student a
where sid in
(
select sid
from
(
select sid
from scott.student
where a.cid=cid
order by age asc
) b
where rownum<=2
)
只能得到所有记录中年龄最小的两条记录为什么??
where sid in (select sid from
(select * from scott.student order by age asc)
where a.cid=cid and rownum<=2)这样写就对了.
*
FROM
(SELECT
a.*,
row_number() over(partition by a.cid order by a.age asc) num
FROM
scott.student a
)
WHERE
num <= 2