如何从关联表中获取公司的首个/随机员工完整信息？

你的核心问题在于原SQL中MIN(person.id)和MIN(person.name)是独立计算的，会分别取所有员工里ID最小的和姓名字典序最小的，自然可能对应不同员工。以下是几种可靠的解决方案：

方法一：窗口函数（通用SQL标准，推荐） #

利用ROW_NUMBER()窗口函数给每个公司的员工分组编号，确保选取的是同一个员工的完整信息。

获取每个公司ID最小的员工 #

WITH ranked_employees AS (
    SELECT
        company.id AS company_id,
        company.name AS company_name,
        person.id AS employee_id,
        person.name AS employee_name,
        -- 按员工ID升序，给每个公司的员工编号，ID最小的排第1
        ROW_NUMBER() OVER (PARTITION BY company.id ORDER BY person.id ASC) AS rn
    FROM company
    LEFT OUTER JOIN person ON person.company_id = company.id
)
SELECT
    company_id,
    company_name,
    employee_id,
    employee_name
FROM ranked_employees
WHERE rn = 1;

获取每个公司随机一名员工 #

只需把排序规则改成随机值（不同数据库语法有差异：MySQL用RAND()，PostgreSQL用RANDOM()，SQL Server用NEWID()）：

WITH ranked_employees AS (
    SELECT
        company.id AS company_id,
        company.name AS company_name,
        person.id AS employee_id,
        person.name AS employee_name,
        ROW_NUMBER() OVER (PARTITION BY company.id ORDER BY RAND()) AS rn
    FROM company
    LEFT OUTER JOIN person ON person.company_id = company.id
)
SELECT
    company_id,
    company_name,
    employee_id,
    employee_name
FROM ranked_employees
WHERE rn = 1;

方法二：子查询关联最小员工ID #

先找出每个公司的最小员工ID，再关联回person表获取对应姓名，确保ID和姓名属于同一员工：

SELECT
    c.id AS company_id,
    c.name AS company_name,
    p.id AS employee_id,
    p.name AS employee_name
FROM company c
LEFT OUTER JOIN (
    SELECT company_id, MIN(id) AS min_emp_id
    FROM person
    GROUP BY company_id
) emp ON c.id = emp.company_id
LEFT OUTER JOIN person p ON emp.min_emp_id = p.id;

方法三：MySQL老版本兼容方案（无窗口函数时用） #

通过GROUP_CONCAT按规则拼接员工信息，再取第一个值，保证ID和姓名对应：

SELECT
    c.id AS company_id,
    c.name AS company_name,
    SUBSTRING_INDEX(GROUP_CONCAT(p.id ORDER BY p.id ASC), ',', 1) AS employee_id,
    SUBSTRING_INDEX(GROUP_CONCAT(p.name ORDER BY p.id ASC), ',', 1) AS employee_name
FROM company c
LEFT OUTER JOIN person p ON p.company_id = c.id
GROUP BY c.id, c.name;

如果要随机员工，把ORDER BY p.id ASC替换为ORDER BY RAND()即可。

内容的提问来源于stack exchange，提问作者Tom Söderlund