欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQL优化和查询(十六)

程序员文章站 2024-02-12 10:48:43
...
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 查询优化改写技巧与案例》 有教无类 落落 著