如何查询table1关联table2状态为ACTIVE的行并按最小DATE排序

现有表结构及数据 #

• table1：包含ID、NAME列，数据如下：

• table2：包含ID、table1.ID、DATE、STATUS列，数据如下：

查询需求 #

编写SQL查询，返回table1中的唯一行，需满足该行关联的table2中存在STATUS = 'ACTIVE'的记录；结果按table2中对应记录的最小DATE升序排序，期望结果如下：

已尝试的查询及问题 #

1. 基于JPA实体关联的查询：

select t1, min(t2.date) 
from table1 t1 join t1.t2List t2  -- table1 Entity has OneToMany t2List defined
where t2.status = 'ACTIVE'
group by t1.id
order by t2.date desc;

问题：无法使用table1的Entity类，且不想新建类存储聚合结果（min date）。

2. 使用IN子句+HAVING的查询：

select t1 from table1 t1
where t1.id in (
select table1.id from table2 t2 where t2.status = 'ACTIVE'
group by t1.id, t2.date
having t2.date = min(t2.date));

问题：HAVING子句未达到预期效果，无法正确筛选出每个table1.ID的最小ACTIVE日期。

可行解决方案 #

方案1：子查询获取最小日期后关联排序 #

先通过子查询找出每个table1.ID对应的最小ACTIVE日期，再关联table1获取数据并排序：

SELECT t1.ID, t1.NAME
FROM table1 t1
JOIN (
    SELECT table1.ID, MIN(DATE) AS min_active_date
    FROM table2
    WHERE STATUS = 'ACTIVE'
    GROUP BY table1.ID
) t2_min ON t1.ID = t2_min.ID
ORDER BY t2_min.min_active_date ASC;

方案2：使用窗口函数筛选最小日期记录 #

利用窗口函数ROW_NUMBER()标记每个table1.ID的最小ACTIVE日期记录，再关联table1：

SELECT DISTINCT t1.ID, t1.NAME
FROM table1 t1
JOIN (
    SELECT 
        table1.ID,
        DATE,
        ROW_NUMBER() OVER (PARTITION BY table1.ID ORDER BY DATE ASC) AS rn
    FROM table2
    WHERE STATUS = 'ACTIVE'
) t2_rn ON t1.ID = t2_rn.ID AND t2_rn.rn = 1
ORDER BY t2_rn.DATE ASC;

这两种方案都不需要依赖JPA实体类，直接通过原生SQL实现需求，且能正确返回期望的排序结果。

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