sql问题

Question

本帖最后由 carl6148 于 2009-06-16 20:22:00 编辑

Answer 1

select distinct DEPTNO,distinct total_sal,distinct total_bonus 
from( 
select e.EMPNO, 
e.ENAME, 
SUM(distinct e.sal) over 
(partition by e.deptno) as total_sal, 
e.DEPTNO, 
SUM(e.sal*case when eb.type=1 then .1 
when eb.type=2 then .2 
else .3 end)over 
(partition by deptno) as total_bonus 
from EMP e,EMP_BONUS eb 
where e.EMPNO=eb.EMPNO 
and e.DEPTNO=10 
)

Answer 2

不行，这样又多了一个错误，只能用一个distinct

Answer 3

select * from EMP_BONUS
7934 2005-05-17 1
7934 2005-02-15 2
7839 2005-02-15 3
7782 2005-02-15 1
select * from EMP
7369 SMITH CLERK 7902 1980-12-17 00:00:00.000 800 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00.000 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00.000 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00.000 2975 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00.000 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00.000 2850 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00.000 2450 NULL 10
7788 SCOTT ANALYST 7566 1982-12-09 00:00:00.000 3000 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 00:00:00.000 5000 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00.000 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00.000 1100 NULL 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00.000 950 NULL 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00.000 3000 NULL 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00.000 1300 NULL 10