几个高难度的SELECT语句

[walkman154]

在scott用户下的emp和dept表中查询

1，查询平均工资最高的那个部门的名称，最高工资，最低工资，平均工资。

select max(sal) as 最高工资,min(sal) as 最低工资,avg(sal) as 平均工资
from emp where deptno in
(select deptno from
(select avg(sal) as avgsal,deptno from emp group by deptno order by avgsal desc) where rownum = 1);
暂时不能显示部门名称

2，求工资由高到低，排名第三的员工姓名

3，求当前系统的日期，不要年和月。

4，查询处员工人数最多的那个部门的
部门编号，部门名称，部门人数

5，查询工资成本最高的那个部门的
部门编号，部门名称，部门月工资成本

6，按 某某人 的上级是 某某人的格式查询处所有员工的信息，
如果某某人没有上级，则上级的名字为“未知”

7，查询处在公司工作时间最长的原工信息
部门名称，员工姓名，在公司工作的时间

8，显示裁员信息，例如，那个部门只有一个就不裁，若部门员工人数大于1人，则裁员工工资最高的那个人。

[feng2]

6、
select a.empno,a.ename,a.mgr,nvl(b.ename,'未知') sj from emp a left join
(select empno,ename,mgr from emp) b
on a.mgr = b.empno
order by a.empno;

7、
select dname,ename,hiredate from deptno b
(select ename,hiredate,deptno,row_number()over(order by hiredate) rn from emp) a
where a.deptno = b.deptno
and rn <=1;

8、
select dname,ename,sal from emp a,
(
select deptno,dname,max(sal) m,min(sal) n from emp,dept
where emp.deptno = dept.deptno
groupt by deptno,dname
) b
where a.deptno = b.deptno and m > n and a.sal = b.m;

[yujang]

2)SQL> select * from
2 (select ename,dense_rank()over(order by sal) cn from emp)
3 where cn=3;

ENAME CN
---------- ----------
ADAMS 3

3)SQL> select to_char(sysdate,'DD') from dual;

TO
--
05

4)SQL> select deptno,count from
2 (select deptno,count(*) count from emp
3 group by deptno
4 order by count desc)
5 where rownum=1;

DEPTNO COUNT
---------- ----------
30 6

5)SQL> select a.deptno,a.avg,b.dname
2 from
3 (select deptno,avg
4 from
5 (
6 select deptno,avg(sal) avg from emp
7 group by deptno
8 order by avg(sal) desc)
9 where rownum=1) a,
10 dept b
11 where a.deptno=b.deptno;

DEPTNO AVG DNAME
---------- ---------- --------------
10 2916.66667 ACCOUNTING

6)

[JoMuncher]

恩 还可以...上次做个项目 做个查询画面..写了200余行的Sql语句,完成了查询功能....结果被组长一顿暴K>>你还让别人怎么维护去........!!! 呵呵

[walkman154]

呵呵，继续期待大家的解决方案！！

[ximan1984]

feng2小心误导读者呀！(select avg(sal) as avgsal,deptno,dname from emp group by deptno,dname order by avgsal desc：emp表中有dname字段么？明显有错误吧。。。或许是我理解有问题？。。

[ximan1984]

这样应该可以显示部门名称了：
select max(sal) as 最高工资,min(sal) as 最低工资,avg(sal) as 平均工资,b.dname
from emp a,dept b where a.deptno in
(select deptno from
(select avg(sal) as avgsal,deptno from emp group by deptno order by avgsal desc) where rownum = 1)and a.deptno=b.deptno;

[sigui]

fct0906(bighammer)：告诉那老大，他的思维是不对的。应用逻辑都交给db了，我们应用层程序员吃什么呀！

[walkman154]

呵呵！

[walkman154]

下面公布答案

在scott用户下的emp和dept表中查询

1，查询平均工资最高的那个部门的名称，最高工资，最低工资，平均工资。
1>使用rownum的方式
select d.dname 部门名称,
min(e.sal) 最低工资,
max(e.sal) 最高工资,
avg(e.sal) 平均工资
from emp e,dept d where e.deptno=d.deptno and e.deptno=
(select deptno from
(select avg(sal) as avgsal,deptno from emp
group by deptno order by avgsal desc) where rownum = 1)
group by d.dname;

2>不使用rownum的方式

select d.dname 部门名称,
min(e.sal) 最低工资,
max(e.sal) 最高工资,
avg(e.sal) 平均工资
from emp e,dept d where e.deptno = d.deptno
and d.deptno = (select aa.deptno from (select avg(sal) as avgsal,deptno from emp group by deptno) aa
where aa.avgsal = (select MAX(avgsal) from
(select avg(sal) as avgsal,deptno from emp group by deptno)))
group by d.dname;


2，求工资由高到低，排名第三的员工姓名

select * from(select * from emp order by sal desc) where rownum < 4
minus
select * from(select * from emp order by sal desc) where rownum < 3

3，求当前系统的日期，不要年和月。

select to_char(sysdate,'----mm--') from dual;

4，查询处员工人数最多的那个部门的
部门编号，部门名称，部门人数

1>不使用rownum的方式
select e.deptno 部门编号,
d.dname 部门名称,
count(*) 部门人数
from emp e,dept d where e.deptno = d.deptno and e.deptno =
(select aa.deptno from
(select count(empno) as empno_count,deptno from emp group by deptno) aa where aa.empno_count =
(select max(empno_count) as max_count from(
select count(empno) as empno_count,deptno from emp group by deptno)))
group by d.dname,e.deptno;

2>使用rownum的方式
select e.deptno 部门编号,
d.dname 部门名称,
count(*) 部门人数
from emp e,dept d where e.deptno = d.deptno and e.deptno =
(select deptno from
(select count(empno) as empno_count,deptno from emp group by deptno order by empno_count desc) where rownum = 1)
group by e.deptno,d.dname;

5，查询工资成本最高的那个部门的
部门编号，部门名称，部门月工资成本
------(工资成本就是所有工资总合)
1>不使用rownum的方式
select e.deptno as 部门编号,
d.dname as 部门名称,
sum(e.sal) as 部门月工资成本
from emp e,dept d where e.deptno = d.deptno and e.deptno =
(select aa.deptno from
(select sum(sal) as sum_sal,deptno from emp group by deptno) aa where aa.sum_sal =
(select max(sum_sal) from
(select sum(sal) as sum_sal,deptno from emp group by deptno)))
group by e.deptno,d.dname;

2>使用rownum的方法
select e.deptno as 部门编号,
d.dname as 部门名称,
sum(e.sal) as 部门月工资成本
from emp e,dept d where e.deptno = d.deptno and e.deptno =
(select deptno from
(select sum(sal) as sum_sal,deptno from emp group by deptno order by sum_sal desc) where rownum = 1)
group by e.deptno,d.dname;

6，按 某某人 的上级是 某某人的格式查询处所有员工的信息，
如果某某人没有上级，则上级的名字为“未知”

select a.ename as 员工 ,nvl(b.ename,'未知')as 上级 from emp a,emp b
where a.mgr = b.empno(+)

7，查询处在公司工作时间最长的原工信息
部门名称，员工姓名，在公司工作的时间

select d.dname as 部门名称,ename as 员工姓名, sysdate-hiredate as 工作时间 from emp e,dept d
where e.deptno = d.deptno
and e.hiredate = (select aa.min_hiredate from (select min(hiredate) min_hiredate from emp) aa
where aa.min_hiredate = (select min(hiredate) from emp))

8，显示裁员信息，例如，那个部门只有一个就不裁，若部门员工人数大于1人，则裁员工工资最高的那个人。

select ename as 员工姓名,deptno as 员工部门,sal as 工资 from emp
where sal in(select max(sal) from emp where deptno in
(select deptno from emp group by deptno having count(*) > 1) group by deptno)

[maxtool]

新手学习~~

[xiaoxiao1984]

1. 查询平均工资最高的那个部门的名称，最高工资，最低工资，平均工资。
select b.dname, c.maxsal, c.minsal, c.mavg from
(select deptno, mavg, maxsal, minsal, dense_rank()over(order by mavg desc) as rid from
(select deptno, trunc(avg(sal),2) as mavg, max(sal)as maxsal, min(sal) as minsal from emp group by deptno ))c
inner join dept b on c.deptno = b.deptno where c.rid = 1 ;

2. 求工资由高到低，排名第三的员工姓名
select ename from ( select dense_rank() over(order by sal desc) as rid, ename from emp ) where rid = 3 ;

3. 求当前系统的日期，不要年和月
select to_char(sysdate,'DD') from dual ;

4. 查询处员工人数最多的那个部门的 部门编号，部门名称，部门人数
select b.deptno, b.dname, a.cid from
(select deptno, cid, dense_rank()over(order by cid desc) as rid from (select deptno, count(*) as cid from emp group by deptno))a, dept b
where a.deptno = b.deptno and a.rid = 1 ;
......

用分析函数解决类似的需求似乎比较简单一点

[flylpp]

作个标记，来学习的^_^

[jinms]

标记一下，学习

[allright_flash]

不是很难的题！
灵活运用order by、min、max、avg、group by having 、start with、connect by 、rownum、count、子查询等等，就能解决问题。

[allright_flash]

minus是个好多西,顶一下

[xiananni]

学习借鉴～～

[walkman154]

ding

[zxbyhcsdn]

不就是考分析函数么!!

[zhigang_33]

mark

[free_kyy]

学习借鉴～～