嵌套查询二级过滤：筛选至少2篇出版物的研究者2000年后发表记录

Schema（表结构） #

• Researcher(ID (PK), )
• Activity (PID (publication_FK, PK) , RID (researcher_FK , PK))
• Publication (ID (PK) , Year)

Activity表中的PID、RID构成其联合主键，分别对应Publication表和Researcher表的外键。

查询需求 #

查询所有至少拥有2篇出版物的研究者，其2000年及以后发表的全部出版物。

现有查询尝试 #

SELECT PID, RID
FROM Activity
WHERE RID IN (SELECT  RID
  FROM Activity
  GROUP BY RID
  HAVING COUNT(RID) >= 2) 

请问如何对该中间结果按照Publication表的Year字段进行过滤？

解决方案 #

你只需要将现有中间结果和Publication表通过PID字段关联，再补充年份过滤条件即可，两种常用实现方式如下：

方式1：直接关联表过滤 #

SELECT a.PID, a.RID, p.Year
FROM Activity a
-- 关联出版物表获取年份信息
INNER JOIN Publication p ON a.PID = p.ID
WHERE a.RID IN (
    SELECT RID
    FROM Activity
    GROUP BY RID
    HAVING COUNT(RID) >= 2
) 
-- 过滤2000年及以后的出版物
AND p.Year >= 2000;

方式2：CTE拆分逻辑可读性更高 #

如果后续需要扩展查询逻辑，用CTE拆分步骤更易维护：

WITH eligible_researchers AS (
    -- 先筛选出总发文量≥2的研究者ID
    SELECT RID
    FROM Activity
    GROUP BY RID
    HAVING COUNT(*) >= 2
)
SELECT a.PID, a.RID, p.Year
FROM Activity a
INNER JOIN eligible_researchers er ON a.RID = er.RID
INNER JOIN Publication p ON a.PID = p.ID
WHERE p.Year >= 2000;

两种写法执行结果完全一致，要是需要带出研究者名称、出版物标题等其他字段，直接在SELECT子句中补充对应表的对应字段即可。

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