师父教的oracle:

师父教的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