Oracle complex queries are SQL

One, over () analysis functions

Group Check before: SELECT * from Test t where (select COUNT (*) from Test a where t.type=a.type and T.scope>a.scope) <2;

--rank ()/dense_rank () over (partition by ...)
SELECT * FROM (select T.*,rank () over (partition by T.type ORDER by T.scope) A from TEST T) a where a.a<3

--dense_rank () Hierarchical sequential sorting
Select T.*,dense_rank () over (partition by T.type ORDER by T.scope) A from test T
--rank () graded Jump sort
Select T.*,rank () over (partition by T.type ORDER by T.scope) A from test T

SELECT * FROM Test t where 2> (SELECT COUNT (*) from Test a where t.type=a.type and T.scope>a.scope)
Select t.* from Test T, (select A.type,max (a.scope) scopes from test a group by A.type) d where T.type=d.type and T.scope=d . Scope

--Cartesian product
SELECT * FROM Test t,test a
Select t.* from Test T, (select A.type,max (a.scope) maxscope,min (a.scope) minscope from test a group by a.type) d where T. Type=d.type and T.scope=d.scope


--
Select T.*,d.maxscope-t.scope Maxscope,t.scope-d.minscope minscope
From Test T,
(Select A.type, max (a.scope) maxscope, Min (a.scope) minscope
From TEST A
Group by A.type) d
where T.type = D.type

--min ()/max () over (partition by ...)
Select T.*,
NVL (Max (t.scope) over (partition by T.type), 0)-T.scope maxscope,
T.SCOPE-NVL (min (t.scope) over (partition by T.type), 0) Minscope
From Test T

--lead ()/lag () over (partition by ...)
Select T.*,lead (t.scope,1,0) over (partition by T.type ORDER by T.scope) a--the same group after one
From Test T
Select T.*,lag (t.scope,1,0) over (partition by T.type ORDER by T.scope) a--the same group as the previous
From Test T

Select T.*,
First_value (T.scope) over (partition by T.type) First_sal,
Last_value (T.scope) over (partition by T.type) Last_sal,
SUM (t.scope) over (partition by T.type) Sum_sal,
AVG (T.scope) over (partition by T.type) Avg_sal,
Count (T.scope) over (partition by T.type) Count_num,
Row_number () over (partition by T.type ORDER by T.scope) row_num
From Test T

--Note: The method with the ORDER BY clause must have an order by when using the method

Oracle complex queries are SQL