SQL 如何对 string_agg() 的结果进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24906826/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sort the result from string_agg()
提问by Vivek S.
I have a table:
我有一张桌子：
CREATE TABLE tblproducts
(
productid integer,
product character varying(20)
)
With the rows:
随着行：
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');
If I execute string_agg()on tblproducts:
如果我执行string_agg()的tblproducts：
SELECT string_agg(product, ' | ') FROM "tblproducts"
It will return the following result:
它将返回以下结果：
CANDID POWDER 50 GM | ESOZ D 20 MG CAP | HHDERM CREAM 10 GM | CREAM 15 GM | KZ LOTION 50 ML | BUDECORT 200 Rotocap
How can I sort the aggregated string, in the order I would get using ORDER BY product?
如何按照我使用的顺序对聚合字符串进行排序ORDER BY product？
I'm using PostgreSQL 9.2.4.
我正在使用 PostgreSQL 9.2.4。
回答by Igor Romanchenko
With postgres 9.0+ you can write:
使用 postgres 9.0+ 你可以写：
select string_agg(product,' | ' order by product) from "tblproducts"
回答by Luuk
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
https://docs.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 ...
回答by Ilesh Patel
select string_agg(prod,' | ') FROM
(SELECT product as prod FROM tblproducts ORDER BY product )MAIN;
