According to each department staff's wages, with showing the highest wages, the department of minimum wage,

CodePudding user response:

This?
With TAB1 as (
Select the 'a' bmid, 1 ygid, 10000 sales from dual union all
Select the 'a' bmid, 2 ygid, 11000 sales from dual union all
Select the 'a' bmid, 3 ygid, 9000 sales from dual union all
Select the 'a' bmid, 4 ygid, 4000 sales from dual union all
Select the 'a' bmid, 5 ygid, 5000 sales from dual union all
Select the 'a' bmid, 6 ygid, 3000 sales from dual union all
Select 'b' bmid, 7 ygid, 10000 sales from dual union all
Select 'b' bmid, 8 ygid, 11000 sales from dual union all
Select 'b' bmid, 9 ygid, 9000 sales from dual union all
Select 'b' bmid, 10 ygid, 4000 sales from dual union all
Select 'b' bmid, ygid 11, 5000, the sales from dual union all
Select 'b' bmid, 12 ygid, 3000 sales from dual
)
The SELECT BMID YGID, SALES, MAX (SALES) over (partition by BMID) MAX, MIN (SALES) over (partition by BMID) MIN FROM TAB1

CodePudding user response:

The emp sort the data in the table, 10 departments from high to low salary as a row, other departments have similar,

CodePudding user response:

 SELECT T. *, ROW_NUMBER () OVER (PARTITION BY JOB ORDER BY SAL DESC) RN FROM SCOTT. The EMP T 

CodePudding user response:

reference 1st floor assission123456 response:

This?
With TAB1 as (
Select the 'a' bmid, 1 ygid, 10000 sales from dual union all
Select the 'a' bmid, 2 ygid, 11000 sales from dual union all
Select the 'a' bmid, 3 ygid, 9000 sales from dual union all
Select the 'a' bmid, 4 ygid, 4000 sales from dual union all
Select the 'a' bmid, 5 ygid, 5000 sales from dual union all
Select the 'a' bmid, 6 ygid, 3000 sales from dual union all
Select 'b' bmid, 7 ygid, 10000 sales from dual union all
Select 'b' bmid, 8 ygid, 11000 sales from dual union all
Select 'b' bmid, 9 ygid, 9000 sales from dual union all
Select 'b' bmid, 10 ygid, 4000 sales from dual union all
Select 'b' bmid, ygid 11, 5000, the sales from dual union all
Select 'b' bmid, 12 ygid, 3000 sales from dual
)
SELECT BMID YGID, SALES, MAX (SALES) over (partition by BMID) MAX, MIN (SALES) over (partition by BMID) MIN FROM TAB1

can also write like this: the SELECT a. mids, a.Y GID, a.S ALES, b.m axsales, b.m insales FROM TAB1 a,
(select bmid, Max (sales) as maxsales, min (sales) as minsales from tab1 group by bmid) b
Where a. mid=b.b mids