在Oracle中能够实现这样的查询吗？

足球小子

原始表两个
emp表：列名：empno,ename,job,mgr(经理编号),hirdate,sal,comm(奖金),deptno
dept表：列名：deptno,dname,loc(工作地点)
用oracle实现！

     查询每个部门的部门号，部门名，员工数以及该部门的平均工资加上工作在该部门的员工的员工名，工资与职位。
     要求显示结果如下：
DEPTNO DNAME       EMPLOYEES AVG_SAL      ENAME  SAL    JOB
------ ----------- --------- -------             ------- ---- --------
   10 ACCOUNTING          3        2916.67       CLARK   2450 MANAGER
                                                              KING    5000 PRESIDENT
                                                              MILLER  1300 CLERK

   20 RESEARCH              5        2175.00       ADAMS   1100 CLERK
                                                              FORD    3000 ANALYST
                                                              JONES   2975 MANAGER
                                                              SCOTT   3000 ANALYST
                                                              SMITH    800 CLERK

   30 SALES                   6        1566.67       ALLEN   1600 SALESMAN
                                                              BLAKE   2850 MANAGER
                                                              JAMES    950 CLERK
                                                              MARTIN  1250 SALESMAN
                                                              TURNER  1500 SALESMAN
                                                              WARD    1250 SALESMAN
   40 OPERATIONS          0       No average

gongzidong

根据俺的知识是不可能！前后的行不一致是不行的！
要想出类似的结果，先建个视图再连接查询吧

doni

这个可能不行
EMPLOYEES,AVG_SAL这两列是要group by的,ENAME,SAL,JOB以是不需要GROUP BY的

[ 本帖最后由 doni 于 2007-4-6 08:22 编辑 ]

doni

突然想起,或许这样可以,就是有点怪

1. break on deptno on dname on employees on sal skip
   
2. select .......
   
3.   from (
   
4.     select d.deptno,d.dname,count(e.empno) employees,decode(count(e.empno),0,"No average",avg(e.sal)) sal
   
5.       from dept d, emp e
   
6.      where d.deptno = e.deptno
   
7.      group by d.deptno,d.dname) a, emp b
   
8. where a.deptno = b.deptno;

复制代码

OCPHUI

有點接近﹐供樓主參考。
select n.depno,n.depname,n.sal_avg,n.emp_cnt,k.empname,k.salary,k.job from
(select m.depno,m.depname,round(avg(m.salary),0) sal_avg,count(*) emp_cnt from  
   (select d.deptno depno,d.dname depname,e.ename empname,e.mgr mgr,
      e.sal salary,e.job job from dept d,emp e where d.deptno(+)=e.deptno) m group by m.depno,m.depname)n,
(select d.deptno depno,d.dname depname,e.ename empname,e.mgr mgr,e.sal salary,e.job job
    from dept d,emp e where d.deptno(+)=e.deptno) k
where n.depno(+)=k.depno

blue_stone

原帖由 doni 于 2007-4-6 08:27 发表
突然想起,或许这样可以,就是有点怪
[code]break on deptno on dname on employees on sal skip
select .......
  from (
    select d.deptno,d.dname,count(e.empno) employees,decode(count(e.empno),0,&qu ...

正解， 使用了sqlplus特有的一些功能

blue_stone

我用hr模式下的表作了一下测试:
sql语句如下:

1. 
   
2. col deptname for a20
   
3. set linesize 200
   
4. set pagesize 200
   
5. break on deptid on deptname on employees on avg_sal skip 1
   
6. select deptid, deptname,employees, avg_sal, employee_id, first_name,salary from
   
7. (select dept.department_id deptid, dept.department_name deptname, count(employee_id) employees, avg(salary) avg_sal
   
8. from hr.employees emp, hr.departments dept
   
9. where emp.department_id = dept.department_id
   
10. group by dept.department_id, dept.department_name) taba, hr.employees tabb
    
11. where taba.deptid = tabb.department_id order by deptid,deptname,employees,avg_sal,employee_id;
    
12. 
    

复制代码

测试结果如下

1. 
   
2. SQL*Plus: Release 9.2.0.6.0 - Production on Fri Apr 6 10:18:30 2007
   
3. 
   
4. Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
   
5. 
   
6. Enter user-name: / as sysdba
   
7. 
   
8. Connected to:
   
9. Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
   
10. With the Partitioning, OLAP and Oracle Data Mining options
    
11. JServer Release 9.2.0.6.0 - Production
    
12. 
    
13. SQL> col deptname for a20
    
14. set linesize 200
    
15. set pagesize 200
    
16. break on deptid on deptname on employees on avg_sal skip 1
    
17. select deptid, deptname,employees, avg_sal, employee_id, first_name,salary from
    
18. (select dept.department_id deptid, dept.department_name deptname, count(employee_id) employees, avg(salary) avg_sal
    
19. from hr.employees emp, hr.departments dept
    
20. where emp.department_id = dept.department_id
    
21. group by dept.department_id, dept.department_name) taba, hr.employees tabb
    
22. where taba.deptid = tabb.department_id order by deptid,deptname,employees,avg_sal,employee_id;SQL> SQL> SQL> SQL>   2    3    4    5    6  
    
23. 
    
24.     DEPTID DEPTNAME              EMPLOYEES    AVG_SAL EMPLOYEE_ID FIRST_NAME                                   SALARY
    
25. ---------- -------------------- ---------- ---------- ----------- ---------------------------------------- ----------
    
26.         10 Administration                1       4400         200 Jennifer                                       4400
    
27. 
    
28.         20 Marketing                     2       9500         201 Michael                                       13000
    
29.                                                               202 Pat                                            6000
    
30. 
    
31.         30 Purchasing                    6       4150         114 Den                                           11000
    
32.                                                               115 Alexander                                      3100
    
33.                                                               116 Shelli                                         2900
    
34.                                                               117 Sigal                                          2800
    
35.                                                               118 Guy                                            2600
    
36.                                                               119 Karen                                          2500
    
37. 
    
38.         40 Human Resources               1       6500         203 Susan                                          6500
    
39. 
    
40.         50 Shipping                     45 3475.55556         120 Matthew                                        8000
    
41.                                                               121 Adam                                           8200
    
42.                                                               122 Payam                                          7900
    
43.                                                               123 Shanta                                         6500
    
44.                                                               124 Kevin                                          5800
    
45.                                                               125 Julia                                          3200
    
46.                                                               126 Irene                                          2700
    
47.                                                               127 James                                          2400
    
48.                                                               128 Steven                                         2200
    
49.                                                               129 Laura                                          3300
    
50.                                                               130 Mozhe                                          2800
    
51.                                                               131 James                                          2500
    
52.                                                               132 TJ                                             2100
    
53.                                                               133 Jason                                          3300
    
54.                                                               134 Michael                                        2900
    
55.                                                               135 Ki                                             2400
    
56.                                                               136 Hazel                                          2200
    
57.                                                               137 Renske                                         3600
    
58.                                                               138 Stephen                                        3200
    
59.                                                               139 John                                           2700
    
60.                                                               140 Joshua                                         2500
    
61.                                                               141 Trenna                                         3500
    
62.                                                               142 Curtis                                         3100
    
63.                                                               143 Randall                                        2600
    
64.                                                               144 Peter                                          2500
    
65.                                                               180 Winston                                        3200
    
66.                                                               181 Jean                                           3100
    
67.                                                               182 Martha                                         2500
    
68.                                                               183 Girard                                         2800
    
69.                                                               184 Nandita                                        4200
    
70.                                                               185 Alexis                                         4100
    
71.                                                               186 Julia                                          3400
    
72.                                                               187 Anthony                                        3000
    
73.                                                               188 Kelly                                          3800
    
74.                                                               189 Jennifer                                       3600
    
75.                                                               190 Timothy                                        2900
    
76.                                                               191 Randall                                        2500
    
77.                                                               192 Sarah                                          4000
    
78.                                                               193 Britney                                        3900
    
79.                                                               194 Samuel                                         3200
    
80.                                                               195 Vance                                          2800
    
81.                                                               196 Alana                                          3100
    
82.                                                               197 Kevin                                          3000
    
83.                                                               198 Donald                                         2600
    
84.                                                               199 Douglas                                        2600
    
85. 
    
86.         60 IT                            5       5760         103 Alexander                                      9000
    
87.                                                               104 Bruce                                          6000
    
88.                                                               105 David                                          4800
    
89.                                                               106 Valli                                          4800
    
90.                                                               107 Diana                                          4200
    
91. 
    
92.         70 Public Relations              1      10000         204 Hermann                                       10000
    
93. 
    
94.         80 Sales                        34 8955.88235         145 John                                          14000
    
95.                                                               146 Karen                                         13500
    
96.                                                               147 Alberto                                       12000
    
97.                                                               148 Gerald                                        11000
    
98.                                                               149 Eleni                                         10500
    
99.                                                               150 Peter                                         10000
    
100.                                                               151 David                                          9500
     
101.                                                               152 Peter                                          9000
     
102.                                                               153 Christopher                                    8000
     
103.                                                               154 Nanette                                        7500
     
104.                                                               155 Oliver                                         7000
     
105.                                                               156 Janette                                       10000
     
106.                                                               157 Patrick                                        9500
     
107.                                                               158 Allan                                          9000
     
108.                                                               159 Lindsey                                        8000
     
109.                                                               160 Louise                                         7500
     
110.                                                               161 Sarath                                         7000
     
111.                                                               162 Clara                                         10500
     
112.                                                               163 Danielle                                       9500
     
113.                                                               164 Mattea                                         7200
     
114.                                                               165 David                                          6800
     
115.                                                               166 Sundar                                         6400
     
116.                                                               167 Amit                                           6200
     
117.                                                               168 Lisa                                          11500
     
118.                                                               169 Harrison                                      10000
     
119.                                                               170 Tayler                                         9600
     
120.                                                               171 William                                        7400
     
121.                                                               172 Elizabeth                                      7300
     
122.                                                               173 Sundita                                        6100
     
123.                                                               174 Ellen                                         11000
     
124.                                                               175 Alyssa                                         8800
     
125.                                                               176 Jonathon                                       8600
     
126.                                                               177 Jack                                           8400
     
127.                                                               179 Charles                                        6200
     
128. 
     
129.         90 Executive                     3 19333.3333         100 Steven                                        24000
     
130.                                                               101 Neena                                         17000
     
131.                                                               102 Lex                                           17000
     
132. 
     
133.        100 Finance                       6       8600         108 Nancy                                         12000
     
134.                                                               109 Daniel                                         9000
     
135.                                                               110 John                                           8200
     
136.                                                               111 Ismael                                         7700
     
137.                                                               112 Jose Manuel                                    7800
     
138.                                                               113 Luis                                           6900
     
139. 
     
140.        110 Accounting                    2      10150         205 Shelley                                       12000
     
141.                                                               206 William                                        8300
     
142. 
     
143. 
     
144. 106 rows selected.
     

复制代码

足球小子

谢blue-stone，我按照你的思路试验出来了，不过不明白下面这句话的含义：
break on deptno on dname on employees on avg_sal skip 1
能帮我解释一下吗，多谢！

blue_stone

break on deptno on dname on employees on avg_sal
表示列  deptno, dname,employees，avg_sal相同的时候只显示第一行，下面显示为空白。
skip 1表示deptno, dname,employees，avg_sal相同的行组成一组，组和组之间有一个空白行。
这个语句设置了sqlplus的显示格式。
具体用法可参考sqlplus reference.

OCPHUI

精辟！