db2 连接后面不是数字的行

gg0vcinb 于 2022-11-07 发布在 DB2

关注(0)|答案(2)|浏览(269)

我有一个表，其中包含我想concat的行。我需要按ID1、ID2分组，并根据行号将每个T类型与父A类型concat。
这是我的一切：

with t as (
    select '123' as ID1, '0' as ID2, 1500 as LINE, 'A' as type, 'First line ' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '0' as ID2, 1510 as LINE, 'T' as type, 'It' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '0' as ID2, 1520 as LINE, 'T' as type, 'works' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '0' as ID2, 1530 as LINE, 'T' as type, 'very' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '0' as ID2, 1540 as LINE, 'T' as type, 'well !' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '0' as ID2, 2000 as LINE, 'A' as type, 'Second line' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '0' as ID2, 2010 as LINE, 'T' as type, 'i am' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '0' as ID2, 2020 as LINE, 'T' as type, 'happy.' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '1' as ID2, 1500 as LINE, 'A' as type, 'Another line' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '1' as ID2, 1510 as LINE, 'T' as type, 'It''s' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '1' as ID2, 1520 as LINE, 'T' as type, 'a' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '1' as ID2, 1530 as LINE, 'T' as type, 'pleasure' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '1' as ID2, 1540 as LINE, 'T' as type, 'to' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '1' as ID2, 1550 as LINE, 'T' as type, 'read' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '1' as ID2, 1560 as LINE, 'T' as type, '!' as txt from sysibm.sysdummy1
    union all
    select '123' as ID1, '1' as ID2, 2100 as LINE, 'A' as type, 'Line without details' as txt from sysibm.sysdummy1
    union all
    select '456' as ID1, '0' as ID2, 1500 as LINE, 'A' as type, 'This is not the same id' as txt from sysibm.sysdummy1
)
select *
from t

这就是我想要的：

with t as (
select '123' as ID1, '0' as ID2, 1500 as LINE, 'First line It works very well !' as txt from sysibm.sysdummy1
union all
select '123' as ID1, '0' as ID2, 2000 as LINE, 'Second line i am happy.' as txt from sysibm.sysdummy1
union all
select '123' as ID1, '1' as ID2, 1500 as LINE, 'Another line It''s a pleasure to read !' as txt from sysibm.sysdummy1
union all
select '123' as ID1, '1' as ID2, 2100 as LINE, 'Line without details' as txt from sysibm.sysdummy1
union all
select '456' as ID1, '0' as ID2, 1500 as LINE, 'This is not the same id' as txt from sysibm.sysdummy1
)
select *
from t

你有主意吗？
谢谢你，

db2

来源：https://stackoverflow.com/questions/68548078/concatenate-rows-with-not-really-following-number

2条答案

按热度按时间

ct3nt3jp1#

假设您需要的是表，而不是SQL语句，请尝试以下操作：

select id1,id2,line / 100 * 100, listagg(txt,' ') within group (order by type,line)
from   t
group  by id1,id2,line / 100

赞(0）回复(0）举报 2022-11-07

xpcnnkqh2#

试试看：

/*
with t as (
...
)

* /

select 
  id1, id2
, min (case type when 'A' then line end) line
, listagg (txt, ' ') within group (order by type, line) txt
from
(
select 
  t.*
, sum (case type when 'A' then 1 else 0 end) over (partition by id1, id2 order by line) grp
from t
)
group by id1, id2, grp

我们在id1, id2中构造一个人工群grp。每次我们在id1, id2中面对type = 'A'，这个群的编号就加1。
dbfiddle链接。

赞(0）回复(0）举报 2022-11-07

我来回答

db2 连接后面不是数字的行

2条答案

相关问题

热门标签

最新问答