sql 聚合函数 STRING_AGG
原创
©著作权归作者所有：来自51CTO博客作者luoganttcc的原创作品，请联系作者获取转载授权，否则将追究法律责任
SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
string_agg
|
apple,pear,banana,pear
|
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
string_agg
|
apple & pear & banana & pear
|
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
string_agg
|
apple & pear & banana
|
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
string_agg
|
pear & pear & apple & banana
|
SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
SELECT
fruit,
STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 0 FOLLOWING AND 3 FOLLOWING) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear",'aa','cc','dfef']) AS fruit;
