SQL优化和查询（十六）

用MERGE改写有聚合操作的UPDATEUPDATE g   SET coll =       (SELECT to_char(wmsys.wm_concat(c.col2 || '_' || nvl (c.col3,c.col4)))         FROM g a          JOIN b ON a.col17 = b.col18         JOIN k c ON b.id = c.col5        WHERE a.col9 = '10A'          AND g.col6 = a.col6)    WHERE g.col9 = '10A'      AND EXISTS (SELECT NULL          FROM g a          JOIN b ON a.col17 = b.col18         JOIN k c ON b.id = c.col5        WHERE a.col9 = '10A'          AND g.col6 = a.col6) )

现在的语句里面有一个wmsys.wm_concat，这是一个聚合函数，作用是把同一组的字符串合并在一起。
现在把关联列应在GROUP BY 后面：

SELECT a.col6,     wmsys.wm_concat(c.col2 || '_' || nvl (c.col3,c.col4)) AS col1         FROM g a          JOIN b ON a.col17 = b.col18         JOIN k c ON b.id = c.col5        WHERE a.col9 = '10A'        GROUP BY g.col6 = a.col6

再把上面的语句放入USING子句就可以了

MERGE INTO gUSING(SELECT a.col6,     wmsys.wm_concat(c.col2 || '_' || nvl (c.col3,c.col4)) AS col1         FROM g a          JOIN b ON a.col17 = b.col18         JOIN k c ON b.id = c.col5        WHERE a.col9 = '10A'        GROUP BY g.col6 = a.col6) xON (x.col6 = g.col6)WHEN MATCHED THEN    UPDATE SET g.col1 = x.col1 WHERE g.col9 = '10A'

选自《Oracle 查询优化改写技巧与案例》 有教无类 落落 著