SQL从关联表取额外列且保证子查询结果计数匹配的实现方法

问题根因 #

你遇到的问题核心有两点：

1. 第一次写的查询直接引用了D.Subject，但FROM子句中没有引入Details表，因此会触发语法错误。
2. 直接用JOIN后行数和原查询不一致，是因为Details表中同一个associate_id可能对应多条不同Subject的记录：原IN子句查询只会为每个符合条件的学生返回1条记录，而关联后会把所有匹配的Details记录全部展开，导致行数翻倍。你写的LEFT JOIN加WHERE过滤的写法，和INNER JOIN效果完全等价，无法解决多匹配的问题。

解决方案 #

根据你对Subject字段的使用需求，有两种常用实现方式，都可以保证返回行数和原查询完全一致：

场景1：需要保留该学生对应的所有科目，合并为单个字段返回 #

不同数据库有对应的字符串聚合函数，示例如下：

MySQL 写法

SELECT S.Student_ID, S.Name, S.School, GROUP_CONCAT(DISTINCT D.Subject SEPARATOR ', ') AS Subjects
FROM Student S
INNER JOIN Details D ON S.Student_ID = D.associate_id
GROUP BY S.Student_ID, S.Name, S.School;

PostgreSQL 写法

SELECT S.Student_ID, S.Name, S.School, STRING_AGG(DISTINCT D.Subject, ', ') AS Subjects
FROM Student S
INNER JOIN Details D ON S.Student_ID = D.associate_id
GROUP BY S.Student_ID, S.Name, S.School;

SQL Server 可直接使用STRING_AGG函数，Oracle 使用LISTAGG函数，逻辑与上述写法一致。

场景2：仅需要返回该学生对应的任意1个/最新1个科目 #

使用窗口函数先对Details表按associate_id分组去重，再关联即可：

SELECT S.Student_ID, S.Name, S.School, D.Subject
FROM Student S
INNER JOIN (
    SELECT 
        associate_id, 
        Subject,
        -- 可修改ORDER BY逻辑调整取数规则，比如按创建时间倒序取最新科目
        ROW_NUMBER() OVER (PARTITION BY associate_id ORDER BY (SELECT 1)) AS rn
    FROM Details
) D ON S.Student_ID = D.associate_id AND D.rn = 1;

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