SQL优化和查询（十六）

用MERGE改写有聚合操作的UPDATE
UPDATE 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 g
USING(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) x
ON (x.col6 = g.col6)
WHEN MATCHED THEN
    UPDATE SET g.col1 = x.col1 WHERE g.col9 = '10A'

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