如何在Oracle中获得相似的价值 [英] How to get a similar value in Oracle

问题描述

我有一个两列的表

Col1  Col2
A        1
A        2
A        3
B        1
B        2
B        3

我需要的输出是这样

Col1    Col2
A       1
A       1,2
A       1,2,3
B       1
B       1,2
B       1,2,3

谢谢.

推荐答案

以下是适用于MySQL的解决方案.它在select子句中使用相关子查询将Col2值分组在一起.逻辑是，对于共享相同Col1值的给定记录组，我们只汇总小于或等于当前行的值.

Here is a solution which would work for MySQL. It uses a correlated subquery in the select clause to group concatenate together Col2 values. The logic is that we only aggregate values which are less than or equal to the current row, for a given group of records sharing the same Col1 value.

SELECT
    Col1,
    (SELECT GROUP_CONCAT(t2.Col2 ORDER BY t2.Col2) FROM yourTable t2
     WHERE t2.Col2 <= t1.Col2 AND t1.Col1 = t2.Col1) Col2
FROM yourTable t1
ORDER BY
    t1.Col1,
    t1.Col2;

演示

这是Oracle中的相同查询:

Here is the same query in Oracle:

SELECT
    Col1,
    (SELECT LISTAGG(t2.Col2, ',') WITHIN GROUP (ORDER BY t2.Col2) FROM yourTable t2
     WHERE t2.Col2 <= t1.Col2 AND t1.Col1 = t2.Col1) Col2
FROM yourTable t1
ORDER BY
    t1.Col1,
    t1.Col2;

演示

请注意，唯一真正的变化是将LISTAGG替换为GROUP_CONCAT.

Note that the only real change is substituting LISTAGG for GROUP_CONCAT.

这篇关于如何在Oracle中获得相似的价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！