显示各部门员工的工资,并附带显示该部门的最高工资,最低工资。
解决方案 »
- 按时间段分组查询(30分)
- Oracle一条很常用的查询 麻烦各位高手
- java如何获取SQL分析结果
- 在线求救!!动态SQL的问题!!!
- 初学Oracle,问下Oracle的结构
- 高分求教!to_char(id.nextval,'000000')在转化的时候产生空格的问题!
- 这条语句什么意思?
- ORACLE统计问题
- Oracle客户端输出中文乱码问题
- 学习oracle除了这个论坛,大家还经常去哪儿啊?英文的也行啊,主要就是人气旺些的!
- ORACLE触发器问题
- oracle :ORA-01089: immediate shutdown in progress - no operations are permitted
这样的?
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
(select bmid,max(sales) as maxsales,min(sales) as minsales from tab1 group by bmid) b
where a.bmid = b.bmid