如何用SQL查询首次出版时年龄最小的作者？涉及三张指定数据库表

没问题，我帮你理清楚这个需求的实现思路，咱们一步步来：

首先先明确下我假设的表结构（如果你的字段名不同，直接替换即可）：

• Authors：author_id（主键）、full_name（作者姓名）、birth_date（出生日期）
• Books：book_id（主键）、publish_date（出版日期）
• BooksAuthors：author_id、book_id（联合外键，关联作者和书籍）

我们的目标是找到所有在首次出版书籍时年龄最小的作者，具体实现步骤如下：

步骤1：获取每个作者的首次出版日期 #

先通过关联表找到每个作者出版过的所有书籍，再筛选出最早的出版日期：

SELECT 
    ba.author_id,
    MIN(b.publish_date) AS first_publish_date
FROM BooksAuthors ba
JOIN Books b ON ba.book_id = b.book_id
GROUP BY ba.author_id

步骤2：计算每个作者首次出版时的年龄 #

把上面的结果和Authors表关联，计算首次出版时的年龄（这里以MySQL为例，其他数据库有类似写法，比如PostgreSQL用EXTRACT(YEAR FROM AGE(first_publish_date, birth_date))）：

SELECT 
    a.author_id,
    a.full_name,
    TIMESTAMPDIFF(YEAR, a.birth_date, fp.first_publish_date) AS age_at_first_publish
FROM Authors a
JOIN (
    SELECT 
        ba.author_id,
        MIN(b.publish_date) AS first_publish_date
    FROM BooksAuthors ba
    JOIN Books b ON ba.book_id = b.book_id
    GROUP BY ba.author_id
) fp ON a.author_id = fp.author_id

步骤3：筛选出年龄最小的作者 #

这里提供两种常用实现方式：

方式一：子查询匹配最小年龄值 #

适合所有版本的SQL数据库，兼容性强：

SELECT 
    a.author_id,
    a.full_name,
    TIMESTAMPDIFF(YEAR, a.birth_date, fp.first_publish_date) AS age_at_first_publish
FROM Authors a
JOIN (
    SELECT 
        ba.author_id,
        MIN(b.publish_date) AS first_publish_date
    FROM BooksAuthors ba
    JOIN Books b ON ba.book_id = b.book_id
    GROUP BY ba.author_id
) fp ON a.author_id = fp.author_id
WHERE TIMESTAMPDIFF(YEAR, a.birth_date, fp.first_publish_date) = (
    SELECT MIN(TIMESTAMPDIFF(YEAR, a2.birth_date, fp2.first_publish_date))
    FROM Authors a2
    JOIN (
        SELECT 
            ba2.author_id,
            MIN(b2.publish_date) AS first_publish_date
        FROM BooksAuthors ba2
        JOIN Books b2 ON ba2.book_id = b2.book_id
        GROUP BY ba2.author_id
    ) fp2 ON a2.author_id = fp2.author_id
)

方式二：窗口函数写法（更简洁） #

适合支持窗口函数的数据库（如MySQL 8+、PostgreSQL、SQL Server等）：

SELECT author_id, full_name, age_at_first_publish
FROM (
    SELECT 
        a.author_id,
        a.full_name,
        TIMESTAMPDIFF(YEAR, a.birth_date, MIN(b.publish_date)) AS age_at_first_publish,
        RANK() OVER (ORDER BY TIMESTAMPDIFF(YEAR, a.birth_date, MIN(b.publish_date))) AS age_rank
    FROM Authors a
    JOIN BooksAuthors ba ON a.author_id = ba.author_id
    JOIN Books b ON ba.book_id = b.book_id
    GROUP BY a.author_id, a.full_name, a.birth_date
) ranked_authors
WHERE age_rank = 1

额外注意事项 #

• 如果你的数据库是Oracle，计算年龄的函数需要调整，比如用TRUNC(MONTHS_BETWEEN(first_publish_date, birth_date)/12)
• 上述查询会自动返回所有首次出版年龄相同且为最小值的作者
• 通过JOIN关联BooksAuthors，自动排除了没有出版过任何书籍的作者

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