如何对 string_agg() 函数的结果进行排序

Question

我有一张表格：

CREATE TABLE tblproducts
(
productid integer,
product character varying(20)
)

使用行：

INSERT INTO tblproducts(productid, product) VALUES (1, 'CANDID POWDER 50 GM');
INSERT INTO tblproducts(productid, product) VALUES (2, 'SINAREST P SYP 100 ML');
INSERT INTO tblproducts(productid, product) VALUES (3, 'ESOZ D 20 MG CAP');
INSERT INTO tblproducts(productid, product) VALUES (4, 'HHDERM CREAM 10 GM');
INSERT INTO tblproducts(productid, product) VALUES (5, 'CREAM 15 GM');
INSERT INTO tblproducts(productid, product) VALUES (6, 'KZ LOTION 50 ML');
INSERT INTO tblproducts(productid, product) VALUES (7, 'BUDECORT 200 Rotocap');

如果我在 tblproducts 上执行 string_agg()：

SELECT string_agg(product, ' | ') FROM "tblproducts"

它将返回以下结果：

CANDID POWDER 50 GM | ESOZ D 20 MG CAP | HHDERM CREAM 10 GM | CREAM 15 GM | KZ LOTION 50 ML | BUDECORT 200 Rotocap

我该如何按照ORDER BY product的顺序排序聚合字符串呢？

我正在使用PostgreSQL 9.2.4。

- Vivek S.

Answer 1

使用 PostgreSQL 9.0+，您可以编写以下代码：

select string_agg(product,' | ' order by product) from "tblproducts"

详细信息请查看此处。

- Ihor Romanchenko

---

请问您能否提供一个解决方案，该方案在使用窗口函数时也能正常工作？ - Saurabh Gujarani

谢谢提供链接。在文档中搜索 string_agg 并不能直接跳转到相关内容。 - Manngo

能否将结果输出保持原样而不进行排序？ - Shantilal Suthar

可以将结果输出保持原样而不进行排序吗？ - undefined

Answer 2

对于Microsoft SQL：使用“WITHIN GROUP”

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

SELECT
  STRING_AGG(prod, '|') WITHIN GROUP (ORDER BY product)
FROM ... 

- Luuk

---

这个问题是关于PostgreSQL的。WITHIN GROUP子句不适用于string_agg函数，就像在Microsoft SQL中一样。 - Manngo

这个问题涉及到 string_agg 函数。Postgres 只是他提到的一个相关内容，而且在他的问题中是最后提到的。这个问题对其他人也有用。 - nomen

如果这个语法给你语法错误，请检查你的兼容性级别：https://dev59.com/slcP5IYBdhLWcg3w8eS4 - Mr. TA

Answer 3

这个似乎即使进行强制转换也可以按数字顺序排序：

select string_agg(cast (o.id as varchar),',' order by o.id) from tb_organisation o 
    inner join tb_country c on o.country_fk = c.id 
        where c.name ilike '%united%' group by c.id;

- gil.fernandes

Answer 4

select string_agg(prod,' | ') FROM 
  (SELECT product as prod FROM tblproducts ORDER BY product )MAIN;

SQL FIDDLE

- Ilesh Patel

---

我遇到了与发帖者相同的问题，这种方法是我最初想到的，但不幸的是它并不起作用（这也是我来这里的原因），而Igor的方法却有效。 - chbrown

在我的情况下，Ilesh和Igor的两种方法都有效。 - Stephan

答案不正确。它可能有效，但不能保证有效。 - zyamys

关系型数据库在某种程度上基于数学集合，这反映在 SQL 的一个基本原则上，即行顺序不重要。即使您在子查询中包含 ORDER BY 子句，FROM 子句也不一定按顺序获取数据。如果这样做有效，那只是纯粹的运气。 - Manngo

我一直很愉快地使用这个解决方案，直到有一天我的系统输入了一些数据，导致它不再起作用。这非常具有欺骗性。（尽管Ilesh辩称文档中说它通常会起作用：“或者，从排序子查询中提供输入值通常会起作用”） - fei0x