顯示各部門員工的工資，并附帶顯示該部門的最高工資，最低工資。
uj5u.com熱心網友回復：
這樣的？
with TAB1 as (
select 'a' bmid, 1 ygid, 10000 sales from dual union all
select 'a' bmid, 2 ygid, 11000 sales from dual union all
select 'a' bmid, 3 ygid, 9000 sales from dual union all
select 'a' bmid, 4 ygid, 4000 sales from dual union all
select 'a' bmid, 5 ygid, 5000 sales from dual union all
select '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, 11 ygid, 5000 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
uj5u.com熱心網友回復：
將emp表中的資料排序，10部門按工資從高到低排，其他部門也類似。uj5u.com熱心網友回復：
SELECT T.*,ROW_NUMBER()OVER(PARTITION BY JOB ORDER BY SAL DESC)RN FROM SCOTT.EMP T
uj5u.com熱心網友回復：
也可以這樣寫：SELECT a.BMID,a.YGID,a.SALES,b.maxsales,b.minsales FROM TAB1 a,(select bmid,max(sales) as maxsales,min(sales) as minsales from tab1 group by bmid) b
where a.bmid = b.bmid
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/46453.html
標籤：基礎和管理