本帖最后由 云中雁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
);