又一道sql题

Question

     一张员工表emp,有id,salary两个字段，计算除最高工资和最低工资之外的所有员工的平均工资？     这道题有陷阱的喔。。，试过再作答吧！！

Answer 1

这个逻辑好像比较简单，不知道在哪个位置可能有陷阱。
下面这样应该没问题，执行计划也不错。OPER@tl> select * from test;        ID     SALARY
---------- ----------
         1       1000
         2       3000
         3      13000
         4       1500
         5       8000
         6       1000已选择6行。OPER@tl> set autot on
OPER@tl> select avg(salary) from test
  2  where salary not in(select max(salary) from test)
  3  and salary not in(select min(salary) from test);AVG(SALARY)
-----------
 4166.66667
执行计划
----------------------------------------------------------
Plan hash value: 2440382220-------------------------------------------------------------------------------
-----------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)
 Time     |
-------------------------------------------------------------------------------
-----------
|   0 | SELECT STATEMENT               |          |     1 |     4 |     7   (0)
 00:00:01 |
|   1 |  SORT AGGREGATE                |          |     1 |     4 |
          |
|*  2 |   INDEX FULL SCAN              | IND_TEST |     4 |    16 |     1   (0)
 00:00:01 |
|   3 |    SORT AGGREGATE              |          |     1 |     4 |
          |
|   4 |     INDEX FULL SCAN (MIN/MAX)  | IND_TEST |     6 |    24 |
          |
|   5 |      SORT AGGREGATE            |          |     1 |     4 |
          |
|   6 |       INDEX FULL SCAN (MIN/MAX)| IND_TEST |     6 |    24 |
          |
-------------------------------------------------------------------------------
-----------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter("SALARY"<> (SELECT MAX("SALARY") FROM "TEST" "TEST") AND "SALARY"
>
              (SELECT MIN("SALARY") FROM "TEST" "TEST"))
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        441  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedOPER@tl>

Answer 2

with s as (select max(salary) smax, min(salary) smin from emp)
可能效率更高些

Answer 3

OPER@tl> select avg(salary) from test
  2  where salary not in(select max(salary) from test)
  3  and salary not in(select min(salary) from test);请问  not in 是否可以为等于呢？
改成等于应该效率比较高吧
？

Answer 4

select avg(salary) from test where salary < (select max(salary) from test) and salary > (select min(salary) from test);

Answer 5

SELECT AVG(SALARY) FROM EMP WHERE SAL<>SELECT MAX(SALARY) FROM EMP AND SAL<>SELECT MIN(SALARY) FROM EMP

Answer 6

SQL> select * from test order by sal;        ID        SAL
---------- ----------
         2       1000
         3       1000
         1       1030
         4       1200
         5
SQL>   select avg(salary)
  2      from (select salary ,rownum r
  3            from (select nvl(sal,0) salary
  4                  from test order by sal asc) where rownum<(select count(sal)
 from test ))
  5      where r>1;AVG(SALARY)
-----------
       1015
可以解决以上两种问题！

Answer 7

改进了下
SQL> edit
Wrote file afiedt.buf  1    select avg(sal)
  2      from (select sal ,rownum r
  3            from (select sal       
  4                  from test order by sal asc) where rownum<(select count(sal)  
 from test ))
  5*     where r>1
SQL> /  AVG(SAL)
----------
      1015

Answer 8

按常理工资当然不会为空，不过我们写sql应该要考虑这些可能出错的问题。

Answer 9

就这也叫陷阱？
  with vMain as (
   select max(salary) maxs,min(salary) mins from emp 
  )
  select avg(a.salary) from emp a,vmain b 
            where a.salary<>b.maxs and a.salary <>b.mins        

Answer 10

如果是空值的话不会有影响，我试过了，应该不能成为陷阱
如果是0的话确实会有影响，可如果有人输入0这个数据，那不符合逻辑，为制造陷阱而制造陷阱
所以1楼的代码正确，不放心可以在条件里加入is not null
12楼的不对，最高工资和最低工资可能重复，明显你那个例子答案应该是1030才对

Answer 11

陷阱说：不知salary是年薪还是月薪。
如果是月薪，同一员工id对应多条salary 那就麻烦了

Answer 12

select avg(salary) from test
 having salary<max(salary)
 and    salary>min(salary)

Answer 13

如果是只去掉一个最高薪水 和一个最低薪水 
select (sum(salary)-max(salary) -salary>min(salary))/count(0)-2 from test 

Answer 14

OPER@tl> select * from test;        ID     SALARY
---------- ----------
         1       1000
         2       3000
         3      13000
         4       1500
         5       8000
         6       1000已选择6行。OPER@tl> set autot on
OPER@tl> select avg(salary) from test
  2  where salary not in(select max(salary) from test)
  3  and salary not in(select min(salary) from test);AVG(SALARY)
-----------
 4166.66667
执行计划
----------------------------------------------------------
Plan hash value: 2440382220-------------------------------------------------------------------------------
-----------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)
 Time     |
-------------------------------------------------------------------------------
-----------
|   0 | SELECT STATEMENT               |          |     1 |     4 |     7   (0)
 00:00:01 |
|   1 |  SORT AGGREGATE                |          |     1 |     4 |
          |
|*  2 |   INDEX FULL SCAN              | IND_TEST |     4 |    16 |     1   (0)
 00:00:01 |
|   3 |    SORT AGGREGATE              |          |     1 |     4 |
          |
|   4 |     INDEX FULL SCAN (MIN/MAX)  | IND_TEST |     6 |    24 |
          |
|   5 |      SORT AGGREGATE            |          |     1 |     4 |
          |
|   6 |       INDEX FULL SCAN (MIN/MAX)| IND_TEST |     6 |    24 |
          |
-------------------------------------------------------------------------------
-----------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter("SALARY"<> (SELECT MAX("SALARY") FROM "TEST" "TEST") AND "SALARY"
>
              (SELECT MIN("SALARY") FROM "TEST" "TEST"))
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        441  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedOPER@tl>

Answer 15

SELECT AVG(nvl(sal,0))
  FROM employee
 WHERE nvl(sal,0) NOT IN (SELECT MAX(sal) FROM employee)
   AND nvl(sal,0) NOT IN (SELECT MIN(sal) FROM employee);
要说陷阱我想最大工资和最小工资重复是不会成为陷阱的，而如果工资为空而又想让这个工资为空的行也计入算平均值的行里面去的话可以用这个方法试试！因为AVG 这个函数它会将要计数的字段为空值的排除在外

Answer 16

7369 wwwww     880          7902
7566 JONES     3272.5  7839
7782 CLARK     3502.95  7839
7788 SCOTT     3520  7566
7839 KING     6897
7876 ADAMS     1210  7788
7902 FORD     3300  7566
7934 MILLER     1972.3  7782
333     yyyyyy          66
5555 frank     9999
4444 wwwww     880          7902
1 frank     9999
2 derek     9950    1
3 wf     8888    2
4 hello     7777    1
SELECT AVG(nvl(sal,0))
  FROM employee
 WHERE nvl(sal,0) NOT IN (SELECT MAX(sal) FROM employee)
   AND nvl(sal,0) NOT IN (SELECT MIN(sal) FROM employee);
值为：4571.795454545454545454545454545454545455最小/大值有多个不会影响排除所有最小/大值
SELECT AVG(sal)
  FROM employee
 WHERE sal NOT IN (SELECT MAX(sal) FROM employee)
   AND sal NOT IN (SELECT MIN(sal) FROM employee);值为：5028.975

Answer 17

select avg(t1.salary) 
from
 (
    select a.*,max(salary) over() m,min(salary) over() n
        from emp a
) t,
  emp t1
where t.id= t1.id
  and t1.salary<>t.m and t1.salary<>t.n