puber们，谈谈oracle开发的面试题

tjshalive

1.
   不知道如果不止一个应该怎么显示。。是多行？多列？还是一个列拼起来？
select dep,max(sal),min(sal),
case when count(emp_name) KEEP(DENSE_RANK first ORDER BY sal) =1 then min(emp_name) KEEP(DENSE_RANK first ORDER BY sal)  else .. end
from emp1
group by dep

   2.
这个是按部门分行打印，不知道是不是需求的意思。。。
又或者是5个一行，无序打印？
select
max(case when rn=1 then EMP_NAME else null end),
max(case when rn=2 then EMP_NAME else null end) ,
max(case when rn=3 then EMP_NAME else null end ),
max(case when rn=4 then EMP_NAME else null end),
max(case when rn=5 then EMP_NAME else null end)  
from
(  select t.* ,row_number()over(partition by dep order by sal) rn
from emp1 t
)
group by dep

fuzhangpeng

第一题答案和3#的差不多。。
select  deptno 部门编号,
min(sal) 最小工资,
wmsys.wm_concat(ename)  keep(dense_rank first order by sal)最小工资的员工,
max(sal) 最大工资,
wmsys.wm_concat(ename)  keep(dense_rank last order by sal)最大工资的员工
from emp
group by deptno;

fuzhangpeng

第二题更简单：
select * from (
        Select ename, rn, row_number() over(Partition By rn Order By ename) rn2
        From   (Select ename, ceil(rn / 5) rn
                    From   (Select ename, rownum As rn From emp)
                  )
)pivot(
max(ename) for rn2 in (1,2,3,4,5)
);

bfc99

交流一下：
1、
SELECT NAME,salary FROM (
SELECT RANK() OVER(ORDER BY salary) low,rank() over(ORDER BY salary desc) high,a.* FROM emp a)
WHERE low=1 OR HIGH=1;

2、
SELECT listagg(name,'   ') WITHIN GROUP (ORDER BY name)
FROM (
SELECT name,CEIL(ROWNUM/5) gr FROM emp)
GROUP BY gr;

ora_pascal1982

--第一题：
with a as (
      select 1000 salary, 'a' name from dual
      union all
      select 2000 , 'b' from dual
      union all
      select 3000 , 'c' from dual
)
select
      a.*,
      max(salary) over () mx,
        min(salary) over() mi,
        first_value(name) over (order by salary desc) mx_name,
        first_value(name) over (order by salary asc ) mi_name      
  from a;

fuzhangpeng

bfc99 发表于 2015-3-11 15:36
交流一下：
1、
SELECT NAME,salary FROM (

第二题的思路学习了。。
第一题没分组考虑，我补了一下

Select deptno,
       ename,
       sal,
       Case
           When low = 1 Then
            'MIN'
           When high = 1 Then
            'MAX'
       End SALN
From   (Select rank() over(Partition By deptno Order By sal) low,
               rank() over(Partition By deptno Order By sal Desc) high,
               a.*
        From   emp a)
Where  low = 1
       Or high = 1;

ora_pascal1982

ora_pascal1982 发表于 2015-3-11 15:43
--第一题：
with a as (
      select 1000 salary, 'a' name from dual

select
        emp.*,
        max(sal ) over (partition by deptno ) mx,
        min(sal) over (partition by deptno ) mi,
        first_value(ename) over (partition by deptno order by sal desc ) mx_name,
        first_value(ename) over (partition by deptno order by sal asc ) mi_name
  from emp;

1        20        3000        800        FORD        SMITH
2        10        5000        1300        KING        MILLER
3        30        2850        950        BLAKE        JAMES

fuzhangpeng

ora_pascal1982 发表于 2015-3-11 15:50
select
        emp.*,
        max(sal ) over (partition by deptno ) mx,

没考虑重复值的问题吧？比如20部门就有两个最大值

ora_pascal1982

fuzhangpeng 发表于 2015-3-11 15:55
没考虑重复值的问题吧？比如20部门就有两个最大值

疏忽了

fuzhangpeng

分享一下你们遇到的面试题呢。。。。