如何在Oracle中将多行合并为单行

Question

如何在Oracle中将多行合并为单行

4

我希望能够将多行合并成一行。

假设我有这样的表格：

ID |  A  |  B  |  C  |  D  |
____________________________
 1 |  x  |  y  |null |  z  |
 2 |null |  z  |null |  x  |
 3 | p   |  w  | a   |null |
 4 | o   |null | k   |null |

现在我需要将所有ID较低的行的更改应用到每一行。第一行是一个基础，因此它应该如下所示：

1 |  x  |  y  |null |  z  |

现在，对于第二行，我需要拿出第一行并从第二行应用更改（如果列不为空）。第二行应该像这样：

2 |  x  |  z  |null |  x  |

第三行 - 取第一行，应用第二和第三行的更改：

3 |  p  |  w  |  a  |  x  |

第四行 - 取第一行，应用第二、三和四行的更改：

4 |  o  |  w  |  k  |  x  |

因此，输出应该如下所示：

1 |  x  |  y  |null |  z  |
2 |  x  |  z  |null |  x  |
3 |  p  |  w  |  a  |  x  |
4 |  o  |  w  |  k  |  x  |

有没有可用于此目的的Oracle功能？

- mad

查看之前的行是LAG函数 - 您可以使用它来获取两个值并执行一些逻辑。 - Randy

3个回答

5

使用分层查询：

CREATE TABLE abcd(
  id int,
   A  varchar2(10),
   B varchar2(10),  
   C varchar2(10),  D varchar2(10)
);

insert all
  into abcd values(1,'x','y',null,'z')
  into abcd values(2,null,'z',null,'x')
  into abcd values(3,'p','w','a',null)
  into abcd values(4,'o',null,'k',null)
select 1 from dual;

commit;

WITH qq(id, a,b,c,d) AS(
   SELECT id, a,b,c,d FROM abcd WHERE id = 1
   UNION ALL
   SELECT x.id, 
          coalesce( x.a, qq.a ),
          coalesce( x.b, qq.b ),
          coalesce( x.c, qq.c ),
          coalesce( x.d, qq.d )
   FROM qq
   JOIN abcd x ON x.id = qq.id + 1
)
SELECT * FROM qq;

       ID A          B          C          D         
---------- ---------- ---------- ---------- ----------
         1 x          y                     z         
         2 x          z                     x         
         3 p          w          a          x         
         4 o          w          k          x

- krokodilko

1

与 LAST_VALUE 类似，您还可以使用 COALESCE 和 LAG。 Oracle 设置：

一个小的 PL/SQL 脚本来创建一个具有预定义列数的表，并用随机数据填充它，这些数据有 50% 的概率是 NULL。

--DROP TABLE table_name;
--DROP SEQUENCE table_name_seq;
SET SERVEROUTPUT ON;
DECLARE
  num_columns INT := 5;
  ct  CLOB := 'CREATE TABLE table_name ( id INT';
  ins CLOB := 'INSERT INTO table_name VALUES ( table_name_seq.nextval';
  val CLOB := 'SELECT id';
BEGIN
  FOR i IN 1 .. num_columns LOOP
    ct  := ct || ', c' || TO_CHAR( i, 'FM00' ) || ' INT';
    ins := ins || ',' || CHR(10) || '  CASE WHEN DBMS_RANDOM.VALUE < .5 THEN NULL ELSE TRUNC( DBMS_RANDOM.VALUE( 0, 100 ) ) END';
    val := val || ',' || CHR(10) || '       COALESCE( c' || TO_CHAR( i, 'FM00' ) || ', LAG( c' || TO_CHAR( i, 'FM00' ) || ' ) IGNORE NULLS OVER ( ORDER BY id ) ) AS c' || TO_CHAR( i, 'FM00' );
  END LOOP;
  ct  := ct || ' )';
  ins := ins || ' )';
  val := val || CHR(10) || 'FROM   table_name;';
  EXECUTE IMMEDIATE ct;
  EXECUTE IMMEDIATE 'CREATE SEQUENCE table_name_seq';
  FOR i IN 1 .. 10 LOOP
    EXECUTE IMMEDIATE ins;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( val );
END;
/

"Query 1":

SELECT * FROM table_name;

结果：

        ID        C01        C02        C03        C04        C05
---------- ---------- ---------- ---------- ---------- ----------
         1                                                       
         2         37         82         85                    21
         3          8                               76         55
         4         98                    66         78           
         5         17                    39                    19
         6         17          3         91                      
         7         76                    42         63         19
         8         18                    95                      
         9         88         24         49                      
        10                    51                    81         49

查询2：

SELECT id,
       COALESCE( c01, LAG( c01 ) IGNORE NULLS OVER ( ORDER BY id ) ) AS c01,
       COALESCE( c02, LAG( c02 ) IGNORE NULLS OVER ( ORDER BY id ) ) AS c02,
       COALESCE( c03, LAG( c03 ) IGNORE NULLS OVER ( ORDER BY id ) ) AS c03,
       COALESCE( c04, LAG( c04 ) IGNORE NULLS OVER ( ORDER BY id ) ) AS c04,
       COALESCE( c05, LAG( c05 ) IGNORE NULLS OVER ( ORDER BY id ) ) AS c05
FROM   table_name;

Results:

        ID        C01        C02        C03        C04        C05
---------- ---------- ---------- ---------- ---------- ----------
         1                                                       
         2         37         82         85                    21
         3          8         82         85         76         55
         4         98         82         66         78         55
         5         17         82         39         78         19
         6         17          3         91         78         19
         7         76          3         42         63         19
         8         18          3         95         63         19
         9         88         24         49         63         19
        10         88         51         49         81         49

将num_columns的值更改为99，您可以在包含100个值的表上看到它正常工作，没有任何问题。

- MT0

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alex Poole · Accepted Answer

您可以使用 last_value() 函数，并选择 ignore nulls 选项来实现此操作：

last_value(a) ignore nulls over (order by id)

将为您提供在当前ID（使用默认窗口）之前看到的a列中的最后一个非空值，因此您可以为要“合并”的每个列重复该操作：

select id,
  last_value(a) ignore nulls over (order by id) as a,
  last_value(b) ignore nulls over (order by id) as b,
  last_value(c) ignore nulls over (order by id) as c,
  last_value(d) ignore nulls over (order by id) as d
from your_table
order by id;

使用您提供的样例数据作为公共表达式：

with your_table (id,  a,  b,  c,  d) as (
            select 1, 'x', 'y', null, 'z' from dual
  union all select 2, null, 'z', null,'x' from dual
  union all select 3, 'p', 'w', 'a', null from dual
  union all select 4, 'o', null, 'k', null from dual
)
select id,
  last_value(a) ignore nulls over (order by id) as a,
  last_value(b) ignore nulls over (order by id) as b,
  last_value(c) ignore nulls over (order by id) as c,
  last_value(d) ignore nulls over (order by id) as d
from your_table
order by id;

        ID A B C D
---------- - - - -
         1 x y   z
         2 x z   x
         3 p w a x
         4 o w k x