使用GROUP_CONCAT(DISTINCT col1 ORDER BY col2)将MariaDB查询转换为PostgreSQL

我正在将一个应用程序从MariaDB迁移到PostgreSQL。
我有一个查询，它对按col 2排序的col 1的DISTINCT值使用GROUP_CONCAT。这个语句在PostgreSQL中是不可能的，所以我必须找到一种替代方法来做到这一点。
MariaDB的查询是这样的：

SELECT
    r.result_id,
    r.sample_id,
    sm.sample_id AS parameter_id,
    GROUP_CONCAT(DISTINCT(sm.metadata_value) ORDER BY smt.display_in_concat SEPARATOR ' ') AS parameter,
    GROUP_CONCAT(DISTINCT(sms.metadata_value) ORDER BY smts.display_in_concat SEPARATOR ' - ') AS sample_info
FROM
    results r
    JOIN samples_metadata sm ON r.parameter_id = sm.sample_id
    JOIN samples_metadata sms ON r.sample_id = sms.sample_id
    JOIN metadata_types smt ON sm.meta_type_id = smt.meta_type_id
    JOIN metadata_types smts ON sms.meta_type_id = smts.meta_type_id
WHERE
    smt.display_in_concat > 0
    AND smts.display_in_concat > 0
    AND r.instance_id = 1625152480
GROUP BY
    r.result_id,
    r.sample_id,
    sm.sample_id;

字符串
输出量：
| 结果ID|样本ID|参数ID|参数|样品信息|
| --|--|--|--|--|
| 111 | 42 | 11 |param1 metric1|名称-代码|
| 112 | 42 | 12 |param2 metric2|名称-代码|
我现在的PostgreSQL查询如下：

SELECT
    r.result_id,
    r.sample_id,
    sm.sample_id AS parameter_id,
    STRING_AGG(DISTINCT sm.metadata_value, ' ') AS parameter,
    STRING_AGG(DISTINCT sms.metadata_value, ' - ') AS sample_info
FROM
    results r
    JOIN samples_metadata sm ON r.parameter_id = sm.sample_id
    JOIN metadata_types smt ON sm.meta_type_id = smt.meta_type_id
    JOIN samples_metadata sms ON r.sample_id = sms.sample_id
    JOIN metadata_types smts ON sms.meta_type_id = smts.meta_type_id
WHERE
    smt.display_in_concat > 0
    AND smts.display_in_concat > 0
    AND r.instance_id = 1625152480
GROUP BY
    r.result_id,
    r.sample_id,
    sm.sample_id;

型
输出量：
| 结果ID|样本ID|参数ID|参数|样品信息|
| --|--|--|--|--|
| 111 | 42 | 11 |metric 1参数1|代码-名称|
| 112 | 42 | 12 |metric 2参数2|代码-名称|
请注意，parameter和sample_info列中的串联值已经切换（Code - Name而不是Name - Code）。这是因为我不能在PostgreSQL的STRING_AGG函数中使用ORDER BY smt.display_in_concat。如果添加ORDER BY部分，则会得到以下错误：

Error: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

型
我在db-fiddle.com上创建了示例。
MariaDB
PostgreSQL的
我需要一个查询，复制MariaDB示例中生成的输出。
谢谢您的支持：）

我找到了一种使用PostgreSQL获得正确结果的方法。
这是查询，它使用子查询来对parameter和sample_info字段进行排序。

SELECT
    r.result_id,
    r.sample_id,
    sm.sample_id AS parameter_id,
    (SELECT string_agg(metadata_value, ' ')
        FROM (SELECT metadata_value, submt.display_in_concat
              FROM samples_metadata ssm
              JOIN metadata_types submt ON ssm.meta_type_id = submt.meta_type_id
              WHERE sample_id = r.parameter_id AND submt.display_in_concat > 0
              ORDER BY submt.display_in_concat) AS subquery) AS parameter,
    (SELECT string_agg(metadata_value, ' - ')
        FROM (SELECT metadata_value, submt.display_in_concat
              FROM samples_metadata ssm
              JOIN metadata_types submt ON ssm.meta_type_id = submt.meta_type_id
              WHERE sample_id = r.sample_id AND submt.display_in_concat > 0
              ORDER BY submt.display_in_concat) AS subquery) AS sample_info
FROM
    results r
    JOIN samples_metadata sm ON r.parameter_id = sm.sample_id
WHERE
    r.instance_id = 1625152480
GROUP BY
    r.result_id,
    r.sample_id,
    sm.sample_id,
    r.parameter_id;

字符串
你可以试试这个db-fiddle：Solution

使用GROUP_CONCAT(DISTINCT col1 ORDER BY col2)将MariaDB查询转换为PostgreSQL

1条答案

相关问题

热门标签

最新问答