连接EMPLOYEE与BOOKS表后，如何用rownum获取正确员工数据及计数

现有表结构及初始化数据 #

建表和插入数据的SQL语句如下：

CREATE TABLE EMPLOYEE
(
    "EMPLOYEE_ID" NUMBER(19,0) NOT NULL,
    "NAME" VARCHAR2(256),
    CONSTRAINT "ID_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE
);

CREATE TABLE BOOKS
(
    "ID" VARCHAR2(32) NOT NULL ENABLE,
    "BOOKNAME" VARCHAR2(256),
    "EMP_ID" NUMBER(19,0) NOT NULL,
    CONSTRAINT "BOOK_ID_PK" PRIMARY KEY ("ID") ENABLE,
    CONSTRAINT "EMP_FK1" FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE
);

INSERT INTO EMPLOYEE VALUES (1, 'James');
INSERT INTO EMPLOYEE VALUES (2, 'John');

INSERT INTO BOOKS VALUES (1, 'JAVA', 1);
INSERT INTO BOOKS VALUES (2, 'CProgramming', 1);
INSERT INTO BOOKS VALUES (3, 'JSP', 1);
INSERT INTO BOOKS VALUES (4, 'Accountancy', 2);
INSERT INTO BOOKS VALUES (5, 'History', 2);
INSERT INTO BOOKS VALUES (6, 'Geography', 2);

问题描述 #

由于BOOKS表数据量较大，希望通过rownum限制加载的图书行数，但执行以下查询无法得到正确的员工数据及计数：

SELECT DISTINCT b.EMP_ID FROM EMPLOYEE e , Books b
WHERE e.EMPLOYEE_ID= b.EMP_ID
AND rownum <= 2;

SELECT COUNT(DISTINCT b.EMP_ID) FROM EMPLOYEE e , Books b
WHERE e.EMPLOYEE_ID= b.EMP_ID
AND rownum <= 2;

期望输出所有关联的员工ID：

EMP_ID
----------
         1
         2

解决方案 #

问题核心是rownum的执行时机：它会在生成结果集的每一行时立即进行判断，而非先去重再限制。直接添加rownum <=2会只返回前2条关联行（均属于EMP_ID=1的员工），自然无法获取EMP_ID=2的数据。

要实现需求，需先确保获取所有关联员工，同时减少BOOKS表的扫描量，可采用以下两种思路：

思路1：通过EXISTS子查询确认员工关联关系 #

如果仅需获取所有有图书的员工ID，无需扫描所有图书行，只需确认员工是否存在关联图书即可：

SELECT e.EMPLOYEE_ID AS EMP_ID
FROM EMPLOYEE e
WHERE EXISTS (
    SELECT 1 FROM BOOKS b 
    WHERE b.EMP_ID = e.EMPLOYEE_ID
);

思路2：先对BOOKS表去重得到员工ID，再处理 #

若必须用rownum限制图书扫描行数（比如需基于图书条件过滤），可先对BOOKS表去重得到所有关联EMP_ID，再进行后续操作：

-- 获取所有关联员工ID
SELECT DISTINCT emp_id AS EMP_ID
FROM (
    SELECT b.EMP_ID 
    FROM BOOKS b
    -- 这里的rownum值只要覆盖所有员工对应的至少一行即可，比如用员工总数作为上限
    WHERE rownum <= (SELECT COUNT(DISTINCT EMP_ID) FROM BOOKS)
);

-- 获取关联员工的计数
SELECT COUNT(*) AS EMP_COUNT
FROM (
    SELECT DISTINCT b.EMP_ID
    FROM BOOKS b
);

关键提示 #

• rownum是Oracle对结果行的实时编号，在查询生成结果行时立即生效，因此先加rownum限制会截断结果集，导致去重后只能得到截断范围内的员工。
• 正确逻辑是先获取所有关联员工ID（通过去重或EXISTS子查询），再按需处理，既避免扫描过多图书行，又能得到完整的员工数据。

内容的提问来源于stack exchange，提问作者user2354566