wm_concat支持distinct函数去重，listagg怎么对结果去重

i2235932666

caroline0803 发表于 2016-1-13 11:42
今天我也遇到了该问题：
这个帖子可以解决该问题：
http://stackoverflow.com/questions/11510870/listag ...

如果加上按 col1分组的语句，就有重复值了，这个应该如何解决？
WITH tab AS
(SELECT 1 AS col1, 2 AS col2, 'Smith' AS created_by
    FROM dual
  UNION ALL
  SELECT 1 AS col1, 2 AS col2, 'John' AS created_by
    FROM dual
  UNION ALL
  SELECT 1 AS col1, 3 AS col2, 'Ajay' AS created_by
    FROM dual
  UNION ALL
  SELECT 1 AS col1, 4 AS col2, 'Ram' AS created_by
    FROM dual
  UNION ALL
  SELECT 1 AS col1, 5 AS col2, 'Jack' AS created_by
    FROM dual)
SELECT listagg(col2_with_nulls, ',') within GROUP(ORDER BY col2_with_nulls)  over(partition by col1) col1
  FROM (SELECT col1,
               CASE
                 WHEN lag(col2) over(ORDER BY col2) = col2 THEN
                  NULL
                 ELSE
                  col2
               END AS col2_with_nulls,
               created_by
          FROM tab);

newkid

i2235932666 发表于 2016-10-17 13:31
如果加上按 col1分组的语句，就有重复值了，这个应该如何解决？
WITH tab AS
(SELECT 1 AS col1, 2 AS ...

你想输出什么？OO似乎在另外一贴已经回答了你，就是用GROUP BY不要用分析函数和PARTITION BY。

WITH tab AS
(SELECT 1 AS col1, 2 AS col2, 'Smith' AS created_by
    FROM dual
  UNION ALL
  SELECT 1 AS col1, 2 AS col2, 'John' AS created_by
    FROM dual
  UNION ALL
  SELECT 1 AS col1, 3 AS col2, 'Ajay' AS created_by
    FROM dual
  UNION ALL
  SELECT 1 AS col1, 4 AS col2, 'Ram' AS created_by
    FROM dual
  UNION ALL
  SELECT 1 AS col1, 5 AS col2, 'Jack' AS created_by
    FROM dual)
SELECT listagg(col2_with_nulls, ',') within GROUP(ORDER BY col2_with_nulls)  
  FROM (SELECT col1,
               CASE
                 WHEN lag(col2) over(ORDER BY col2) = col2 THEN
                  NULL
                 ELSE
                  col2
               END AS col2_with_nulls,
               created_by
          FROM tab)
group by col1;

i2235932666

newkid 发表于 2016-10-17 22:14
你想输出什么？OO似乎在另外一贴已经回答了你，就是用GROUP BY不要用分析函数和PARTITION BY。

WITH t ...

是的，但加上分析函数listagg的结果就会有重复，这确实是个小瑕疵啊

a10322495

i2235932666 发表于 2016-10-17 13:31
如果加上按 col1分组的语句，就有重复值了，这个应该如何解决？
WITH tab AS
(SELECT 1 AS col1, 2 AS ...

2,3,4,5
2,3,4,5
2,3,4,5
2,3,4,5
2,3,4,5
------------------没有重复呀