UC知道一个复杂的SQL查询，请指教
来源：百度知道 编辑：UC知道 时间：2024/05/22 06:43:35
有如下表：
<EMP表-职员>
EMPNO NAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
<DEPT表-部门>
DEPTNO
<EMP表-职员>
EMPNO NAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
<DEPT表-部门>
DEPTNO
1.SELECT e1.EMPNO,e1.NAME,e1.MGR,e2.NAME
FROM EMP e1,EMP e2 ---表明的起别名,因为自己连接自己时,有重复的栏位名需要区分,不起别名无法区分是哪个表的栏位
WHERE e1.MGR=e2.EMPNO
---让管理者编号和员工编号联立,得到管理者的名字.
2.SELECT FIRST 1 a.DNAME,SUM(b.SAL) SAL
---FIRST 1,取返回结果的第一笔数据,不同数据库可能写法不一样
FROM DEPT a,EMP b
WHERE a.DEPTNO=b.DEPTNO
GROUP BY a.DNAME
---按照部门分组进行求和
ORDER BY SAL DESC
---将求和数据按照由大到小的顺序排序,则first 1 返回的就是最大的部门代码和工资总和
1.select EMPNO,NAME,MGR,MGR,NAME from emp where empno=7369
2.select a.DNAME,b.MAXSAL from dept a inner join (select top 1 deptno,sum(sal) as MAXSAL from emp group by deptno order by MAXSAL) b on a.deptno=b.deptno
第一个问题的要求你没说清楚。