一个复杂的标量子查询改写 left join 的例子，在 case when 部分多张表与外表关联，并且有聚合，标量子查询SQL如下，要跑600秒：

SELECT 
    AAZ661 ZFLSH, 
    '1' ZFXH, 
    AAE002 TIMESTAMP, 
    AAE924 JSXM,
    SUM(
        CASE 
            WHEN (
                SELECT COUNT(DISTINCT B.BAA526) 
                FROM t1 A, t2 B 
                WHERE A.AAA345 = B.AAA345 
                  AND A.AAZ661 = Q.AAZ661 
                  AND (B.BAA531 = Q.LTXBZ OR B.BAA531 = '9' OR Q.LTXBZ = '9') 
                  AND B.BAA526 IN ('3', '4')
            ) = 2 
            THEN 
                CASE 
                    WHEN BAA526 = '4' THEN AAE774 
                    WHEN AAE924 NOT IN ('7004', '7008', '7009', '7010', '7011', '7029') AND BAA526 = '5' THEN AAE019 - ZZZHBF 
                    ELSE AAE019 
                END 
            ELSE AAE019 
        END
    ) JE 
FROM Q 
GROUP BY AAZ661, AAE002, AAE924;

第一次改写：

SELECT 
    Q.AAZ661 ZFLSH, 
    '1' ZFXH, 
    Q.AAE002 TIMESTAMP, 
    Q.AAE924 JSXM,
    SUM(
        CASE 
            WHEN V.COUNT_BAA526 = 2 
            THEN 
                CASE 
                    WHEN B.BAA526 = '4' THEN Q.AAE774 
                    WHEN Q.AAE924 NOT IN ('7004', '7008', '7009', '7010', '7011', '7029') AND B.BAA526 = '5' THEN Q.AAE019 - Q.ZZZHBF 
                    ELSE Q.AAE019 
                END 
            ELSE Q.AAE019 
        END
    ) JE 
FROM Q 
LEFT JOIN (
    SELECT 
        A.AAZ661,B.BAA531
        COUNT(DISTINCT B.BAA526) AS COUNT_BAA526
    FROM t1 A
    JOIN t2 B ON A.AAA345 = B.AAA345
    WHERE  B.BAA526 IN ('3', '4')
    GROUP BY A.AAZ661,B.BAA531
) V ON Q.AAZ661 = V.AAZ661 and(Q.LTXBZ = V.BAA531 OR V.BAA531 = '9' OR Q.LTXBZ = '9')
GROUP BY Q.AAZ661, Q.AAE002, Q.AAE924;

改写后只要50秒，但是结果不对，需要修正（chatgpt帮忙做的修正，值得一提的是提问方法，一开始问他原始 SQL 应该怎么改，总是给出明显有错误的 SQL；但后来我先给出一个改写后的 SQL，问他改写的是否正确时，他就能给出下面这个答案了）：

• 子查询中的 GROUP BY 只需要按 A.AAZ661 进行分组，因为我们计算的是 COUNT(DISTINCT B.BAA526)，并且获取 BAA531 的最大值。
• 在子查询中使用 MAX(B.BAA531) 获取 BAA531 的最大值，这样在 LEFT JOIN 时可以正确地进行条件判断。

其实没太理解为什么要取 MAX(B.BAA531) ，尽管它的结果是对的。按道理来说，原始 SQL 是取了 Q 表每行数据中的 Q.AAZ661、Q.LTXBZ 代入子查询，对满足条件的记录计算 COUNT(DISTINCT B.BAA526) ，那改成 left join 后，应该对 AAZ661、BAA531 进行分组，第一次改写的 SQL 就是这个逻辑，但是结果却不对。希望有老师能指点下，不胜感激。

SELECT 
    Q.AAZ661 AS ZFLSH, 
    '1' AS ZFXH, 
    Q.AAE002 AS TIMESTAMP, 
    Q.AAE924 AS JSXM,
    SUM(
        CASE 
            WHEN V.COUNT_BAA526 = 2 
            THEN 
                CASE 
                    WHEN Q.BAA526 = '4' THEN Q.AAE774 
                    WHEN Q.AAE924 NOT IN ('7004', '7008', '7009', '7010', '7011', '7029') AND Q.BAA526 = '5' THEN Q.AAE019 - Q.ZZZHBF 
                    ELSE Q.AAE019 
                END 
            ELSE Q.AAE019 
        END
    ) AS JE 
FROM Q 
LEFT JOIN (
    SELECT 
        A.AAZ661,
        COUNT(DISTINCT B.BAA526) AS COUNT_BAA526,
        MAX(B.BAA531) AS BAA531
    FROM t1 A
    JOIN t2 B ON A.AAA345 = B.AAA345
    WHERE B.BAA526 IN ('3', '4')
    GROUP BY A.AAZ661
) V ON Q.AAZ661 = V.AAZ661 
   AND (V.BAA531 = Q.LTXBZ OR V.BAA531 = '9' OR Q.LTXBZ = '9')
GROUP BY Q.AAZ661, Q.AAE002, Q.AAE924;