如何按Contact ID获取ger01表的最小日期？SQL查询求助

没问题，我来帮你调整这个SQL语句。你的需求是从按ger01_gerenciamentocliente维度取最小日期，改成按Contact ID维度聚合，获取每个联系人对应的所有ger01记录里的最小lftm_mes_referencia_c日期，对吧？

方案一：直接关联聚合（更高效简洁） #

如果你的自定义表ger01_gerenciamentocliente_cstm和主表ger01_gerenciamentocliente是1对1关联（每个主表记录对应一条自定义表记录），可以用这个写法：

SELECT 
    T3.id AS contact_id,
    MIN(T0.lftm_mes_referencia_c) AS min_referencia_month
FROM 
    contacts T3
INNER JOIN 
    ger01_gerenciamentocliente_contacts_c T2 
    ON T3.id = T2.ger01_gerenciamentocliente_contactscontacts_ida 
    AND T2.deleted = 0
INNER JOIN 
    ger01_gerenciamentocliente T1 
    ON T1.id = T2.ger01_gere60bccliente_idb 
    AND T1.deleted = 0
INNER JOIN 
    ger01_gerenciamentocliente_cstm T0 
    ON T0.id_c = T1.id
WHERE 
    T0.lftm_mes_referencia_c IS NOT NULL
GROUP BY 
    T3.id
ORDER BY 
    T3.id ASC;

方案二：基于原SQL逻辑扩展 #

如果自定义表可能存在同一主表ID对应多条记录的情况，可以在原SQL基础上增加外层聚合，保留原有子查询的最小日期计算：

SELECT 
    T3.id AS contact_id,
    MIN(T0.MIN_MONTH) AS min_referencia_month
FROM 
    (SELECT 
         id_c, 
         MIN(lftm_mes_referencia_c) AS MIN_MONTH 
     FROM 
         ger01_gerenciamentocliente_cstm 
     GROUP BY 
         id_c) T0
INNER JOIN 
    ger01_gerenciamentocliente T1 
    ON T0.id_c = T1.id 
    AND T1.deleted = 0
INNER JOIN 
    ger01_gerenciamentocliente_contacts_c T2 
    ON T1.id = T2.ger01_gere60bccliente_idb 
    AND T2.deleted = 0
INNER JOIN 
    contacts T3 
    ON T3.id = T2.ger01_gerenciamentocliente_contactscontacts_ida 
    AND T3.deleted = 0
WHERE 
    T0.MIN_MONTH IS NOT NULL
GROUP BY 
    T3.id
ORDER BY 
    T3.id ASC;

关键调整说明 #

• 核心改动：将分组维度从ger01_gerenciamentocliente的ID（id_c）改为Contact ID（T3.id），通过MIN()聚合函数计算每个联系人对应的所有关联记录的最小日期。
• 方案一优化了表关联顺序，从contacts表出发关联后续表，逻辑更贴合按联系人维度聚合的需求，性能也更优。
• 保留了所有deleted = 0的过滤条件，确保只查询未删除的有效数据；同时过滤掉lftm_mes_referencia_c IS NULL的记录，避免无效值影响结果。

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