如何通过截取的子串字段关联主查询与子查询并获取最大日期

现有一张表，结构及数据如下：

需从Data字段提取ID，并用该ID关联查询获取对应ID的最大日期。目前ID提取正常，但子查询匹配时触发错误：Unsupported: Scalar subquery with multi-column SELECT clause.

当前使用的SQL语句：

SELECT 
    table2.org_name,
    table3.user_email,
    SUBSTRING(table1.data, CHARINDEX('metadata', table1.data), 5) as object_id,
    (SELECT MAX(datetime)
        FROM table1 
        WHERE SUBSTRING(table.data, CHARINDEX('metadata', table.data), 5) = pp.id AND event_type LIKE 'created-object' AND datetime > '2024-10-08') 
        as most_recent_created
FROM 
    table1
    LEFT JOIN pp ON ie_presentation_id = pp.id
    
    ...LEFT JOIN table2, LEFT JOIN table3 ... etc

期望结果：

问题根因 #

标量子查询报错是因为子查询存在字段引用错误（如table.data应为table1.data），且重复调用字符串函数提取ID不仅冗余，还可能导致逻辑混乱。此外，标量子查询要求必须返回单个值，若逻辑不当易触发多列返回错误。

优化实现 #

方法1：CTE提取ID + 分组聚合

先通过CTE统一提取ID，再直接分组计算最大日期，逻辑清晰且性能更优：

WITH extracted_ids AS (
    SELECT 
        *,
        -- 优化ID提取逻辑，确保精准截取ID值
        TRIM(SUBSTRING(Data, CHARINDEX('ID: ', Data) + 4, LEN(Data) - CHARINDEX('ID: ', Data) - 4)) AS object_id
    FROM table1
)
SELECT 
    object_id AS id,
    MAX(Date) AS max_date
FROM extracted_ids
WHERE event_type LIKE 'created-object' AND Date > '2024-10-08'
GROUP BY object_id;

方法2：CTE + JOIN关联其他表

如果需要关联table2、table3等表，可拆分步骤先计算最大日期，再关联查询：

WITH extracted_ids AS (
    SELECT 
        *,
        TRIM(SUBSTRING(Data, CHARINDEX('ID: ', Data) + 4, LEN(Data) - CHARINDEX('ID: ', Data) - 4)) AS object_id
    FROM table1
),
id_max_dates AS (
    SELECT 
        object_id,
        MAX(Date) AS max_date
    FROM extracted_ids
    WHERE event_type LIKE 'created-object' AND Date > '2024-10-08'
    GROUP BY object_id
)
SELECT 
    i.object_id AS id,
    i.max_date,
    t2.org_name,
    t3.user_email
FROM id_max_dates i
LEFT JOIN table1 ON i.object_id = TRIM(SUBSTRING(table1.Data, CHARINDEX('ID: ', table1.Data) + 4, LEN(table1.Data) - CHARINDEX('ID: ', table1.Data) - 4))
LEFT JOIN table2 ON ... -- 补充你的关联条件
LEFT JOIN table3 ON ... -- 补充你的关联条件
GROUP BY i.object_id, i.max_date, t2.org_name, t3.user_email;

关键修正点 #

• ID提取逻辑优化：原固定长度的SUBSTRING易提取错误内容，改用CHARINDEX定位ID: 后动态计算截取长度，确保ID提取准确。
• 避免重复计算：用CTE提前完成ID提取，减少字符串函数的重复调用，提升查询性能。
• 替换标量子查询：改用分组聚合或预计算的方式获取最大日期，彻底规避标量子查询的多列返回错误。

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