师父教的oracle:
1.查询所有学生001课程和002课程比较分高的课程
select *
from (select
s.*,
row_number() over(partition by s.s# order by s.score desc) as rn_
from te_sc s
where s.c# in ('001', '002')
)
where rn_ = 1
2.EXISTS的使用
select * from te_area t WHERE EXISTS (
SELECT 1 FROM
(SELECT LEVEL AS aa FROM dual WHERE LEVEL BETWEEN 50 AND 50 CONNECT BY LEVEL< 100)a
WHERE t.parentid = a.aa
)
3.自创一个表
WITH aa AS
(
SELECT 1 AS a FROM dual
)
SELECT * FROM aa