mysql string agg_如何从string_agg（）中以正确的顺序得到结果

如何从string_agg()中以正确的顺序得到结果

例如，

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中的一些示例行

如果我执行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

用postgres 9.0+你可以写：

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

详情请见： http : //www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

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

SQL FIDDLE