Postgresql 排序 string_agg

Question

我运行以下查询 string_agg(DISTINCT grades, '|')，它按 01|02|03|04|05|KG|PK 的顺序执行并输出我的结果。

如何以这种方式重新排列 PK|KG|01|02|03|04|05？

SELECT
      U.CUSTOM_100000001 AS USERID
    , SC.TITLE
    , U.FIRST_NAME
    , U.LAST_NAME
    , string_AGG(DISTINCT SGL.short_name, '|')
FROM
      USERS U
    , COURSE_PERIODS CP
    , SCHOOLS SC
    , school_gradelevels SGL
WHERE
    CP.SCHOOL_ID=SC.ID
        AND
    U.STAFF_ID = CP.TEACHER_ID
        AND
    SGL.SCHOOL_ID = SC.ID
        AND
    CP.SYEAR =2015
        AND
    SGL.short_name in('PK','KG','01','02','03','04','05','06','07','08')
        AND
    SC.CUSTOM_327 IN ('0021','0025','0051','0061','0071','0073','0081','0101','0111','0131','0211','0‌​221','0294','0301','0321','0341','0361','0371','0291')
GROUP BY
    U.CUSTOM_100000001, SC.TITLE, U.FIRST_NAME, U.LAST_NAME

Answer 1

在 PostgreSQL 9.0+ 中是可能的:

SELECT
    string_agg(DISTINCT SGL.short_name
            , '|' ORDER BY
                (substring(SGL.short_name, '^[0-9]+'))::int NULLS FIRST,
                 substring(SGL.short_name, '[^0-9_]+$') DESC)
FROM school_gradelevels SGL;

测试示例:

WITH tbl(grade) AS (
    VALUES
     ('01'), 
     ('02'), 
     ('03'), 
     ('PK'), 
     ('KG') 
)
SELECT grade
FROM tbl
ORDER BY (substring(grade, '^[0-9]+'))::int NULLS FIRST, substring(grade, '[^0-9_]+$') DESC;

结果:

 grade
-------
 PK
 KG
 01
 02
 03
(5 rows)

Aggregate Expressions