oracle查询员工表中的部门ID,查询职工最少部门ID最小的部门的详细信息，求最优方法。。。...

本帖最后由 云中雁012345 于 2013-8-26 20:23 编辑

现有2张表：数据如下：

3C5{SSSAO}6LI}MDY6K42.jpg (28.07 KB, 下载次数: 9)

work

2013-8-26 20:13 上传

$H}FPI~1E`]H@2PXX3BY4.jpg (7.78 KB, 下载次数: 8)

dept2

2013-8-26 20:13 上传

要求如下：--查询拥有最多的员工的部门的基本信息(要求只取出一个部门的信息)，

--如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息。

我的作法是：--------------------------------------

SELECT did,

dname,

dcity

FROM

(SELECT did,

dname,

dcity,

(SELECT COUNT(*) FROM WORK WHERE did=A.did

)num

FROM dept2 A

ORDER BY num,

did

)

WHERE ROWNUM=1;

能不能在蓝色字体上改一改，实现题目的要求？求高人指点。。。

--表和数据如下：

CREATE TABLE emp2

(

eid   CHAR(6) PRIMARY KEY,

ename VARCHAR2(10),

bdate DATE,

sex   CHAR(1) CHECK (sex IN('F','M')),

city  VARCHAR2(20)

);

------------------------------------------------------------

INSERT

INTO emp2 VALUES

(

'A00001',

'SMITH',

to_date('2010-09-10','yyyy-mm-dd'),

'F',

'上海'

);

INSERT

INTO emp2 VALUES

(

'A00002',

'PXB',

to_date('1990-08-01','yyyy-mm-dd'),

'M',

'广州'

);

INSERT

INTO emp2 VALUES

(

'A00003',

'WNN',

to_date('2010-10-9','yyyy-mm-dd'),

'F',

'天津'

);

INSERT

INTO emp2 VALUES

(

'B00001',

'ZQK',

to_date('1999-5-7','yyyy-mm-dd'),

'M',

'南京'

);

INSERT

INTO emp2 VALUES

(

'B00002',

'LQQ',

to_date('2016-10-3','yyyy-mm-dd'),

'M',

'山西'

);

INSERT

INTO emp2 VALUES

(

'B00003',

'ZRT',

to_date('2011-7-19','yyyy-mm-dd'),

'F',

'陕西'

);

INSERT INTO EMP2 VALUES

('C00001','ABC',SYSDATE,'F','运城'

);

INSERT INTO EMP2 VALUES

('C00002','BCD',SYSDATE,'F','运城'

);

INSERT INTO emp2 VALUES

('C00003','CDE',sysdate,'F','运城'

);

-----------------------------------------------

CREATE TABLE dept2

(

did   CHAR(3) PRIMARY KEY,

dname VARCHAR2(20),

dcity VARCHAR2(20)

);

INSERT INTO dept2 VALUES

('001','销售部','上海'

);

INSERT INTO dept2 VALUES

('002','生产部','北京'

);

INSERT INTO dept2 VALUES

('003','人事部','山东'

);

-------------------------------------------

CREATE TABLE WORK

(

eid       CHAR(6),

did       CHAR(3),

startdate DATE,

salary    INT,

PRIMARY KEY(eid,did),

FOREIGN KEY (eid) REFERENCES emp2(eid),

FOREIGN KEY (did) REFERENCES dept2(did)

);

INSERT INTO WORK VALUES

('A00001','001',to_date('0207','mm/yy'),5000

);

INSERT INTO WORK VALUES

('A00002','001',to_date('0310','mm/yy'),6000

);

INSERT INTO WORK VALUES

('A00003','002',to_date('0499','mm/yy'),20000

);

INSERT INTO WORK VALUES

('B00001','002',to_date('0810','mm/yy'),5000

);

INSERT INTO WORK VALUES

('B00002','003',to_date('1010','mm/yy'),2000

);

INSERT INTO WORK VALUES

('B00003','003',to_date('0913','mm/yy'),3000

);

INSERT INTO WORK VALUES

('C00001','001',SYSDATE,4300

);

INSERT INTO WORK VALUES

('C00002','001',SYSDATE,30000

);

INSERT INTO WORK VALUES

('C00003','001',sysdate,1000

);