如何编写查询语句从两个关联表中选取各站点的首条测量记录

首先明确前提：默认「第一条测量记录」指对应站点最早产生的测量记录，排序规则可根据实际业务替换为自增ID升序等规则；这里统一将table1关联table2主键的字段命名为site_id，table1存储测量时间的字段命名为measure_time。 #

方法1：窗口函数法（兼容 PostgreSQL、MySQL 8.0+、SQL Server 等主流新版本数据库） #

这是目前性能和可读性最优的通用写法，用窗口函数按站点分组排序后取首条即可：

WITH ranked_measurements AS (
    SELECT 
        t1.*,
        -- 按站点分组，按测量时间升序排序打行号
        ROW_NUMBER() OVER (PARTITION BY t1.site_id ORDER BY t1.measure_time ASC) AS rn
    FROM table1 t1
    -- 如需包含没有任何测量记录的站点，新增LEFT JOIN table2 t2 ON t1.site_id = t2.id
)
SELECT * FROM ranked_measurements WHERE rn = 1;

方法2：关联子查询法（兼容所有数据库版本，包括MySQL 5.x等旧版本） #

适合不支持窗口函数的低版本数据库使用：

SELECT t1.*
FROM table1 t1
INNER JOIN table2 t2 ON t1.site_id = t2.id
WHERE t1.measure_time = (
    SELECT MIN(measure_time) 
    FROM table1 
    WHERE site_id = t1.site_id
);

注意：如果同一站点同一时间存在多条测量记录，该写法会返回所有匹配的记录。如果要强制仅返回一条，可将判断条件替换为自增ID最小：WHERE t1.id = (SELECT MIN(id) FROM table1 WHERE site_id = t1.site_id)

方法3：GROUP BY 关联法 #

同样兼容全版本数据库，性能比关联子查询更稳定：

SELECT t1.*
FROM table1 t1
-- 先聚合出每个站点的最早测量时间
INNER JOIN (
    SELECT site_id, MIN(measure_time) AS first_measure_time
    FROM table1
    GROUP BY site_id
) t_first ON t1.site_id = t_first.site_id AND t1.measure_time = t_first.first_measure_time
INNER JOIN table2 t2 ON t1.site_id = t2.id;

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