如何通过

Question

例如摆脱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

一些示例行

• 如果我执行string_agg()tblproducts

  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

Answer 1

随着9.0+你可以写Postgres的：

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

详情点击这里：http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

Answer 2

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

SQL FIDDLE