Snowflake查询需求：匹配i_sup与ship_to数值段取最大SHIP_DATE

表结构 #

• zrmm表：包含matnr、ship_to、ship_date字段
• p_psl表：包含i_sup字段

查询需求 #

当p_psl表的i_sup纯数值与zrmm表ship_to的前缀数值（ship_to可能带字母后缀，如61701 A、61701 B）匹配时，获取对应matnr的最大ship_date。

示例场景 #

同一matnr下，zrmm表中61701 A的ship_date为2024-01-22，61701 B的ship_date为2023-04-23；p_psl表的i_sup为61701。此时需返回该matnr对应的最大日期2024-01-22。

字段处理逻辑与完整查询SQL #

字段格式化规则 #

对ship_to和i_sup做统一处理：

• 若字段可转换为数值，则转为数值后转回字符串，左补0至5位
• 若无法转为数值，保留原字段值

完整查询语句 #

WITH formatted_zrmm AS (
    SELECT
        CASE
            WHEN TRY_CAST(ship_to AS NUMBER) IS NOT NULL THEN
                LPAD(CAST(CAST(PARSE_JSON('"' || ship_to || '"') AS NUMBER) AS VARCHAR), 5, '0')
            ELSE
                ship_to
        END AS formatted_ship_to,
        matnr,
        ship_date
    FROM zrmm
),
formatted_psl AS (
    SELECT
        CASE
            WHEN TRY_CAST(i_sup AS NUMBER) IS NOT NULL THEN
                LPAD(CAST(CAST(PARSE_JSON('"' || i_sup || '"') AS NUMBER) AS VARCHAR), 5, '0')
            ELSE
                i_sup
        END AS formatted_i_sup
    FROM p_psl
)
SELECT
    fz.matnr,
    MAX(fz.ship_date) AS max_ship_date
FROM formatted_zrmm fz
JOIN formatted_psl fp 
    ON fz.formatted_ship_to LIKE fp.formatted_i_sup || '%'
GROUP BY fz.matnr;

实现逻辑 #

1. 通过CTE分别格式化两张表的目标字段，确保格式统一
2. 使用LIKE关联格式化后的字段，匹配ship_to前缀与i_sup一致的记录
3. 按matnr分组，聚合得到每组的最大ship_date

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