SQL连接同一文件中不同记录的文本字段 [英] SQL concatenate text fields from different records in the same file

问题描述

这里正在使用DB2讨论iSeries上文件中的数据.

Were talking about data in a file on an iSeries here using DB2.

文件结构如下:

Item     Textline    Text

12755    1           this item
12755    2           is no longer
12755    3           for sale
abc123   1           please use
abc123   2           another code
xyz987   1           obsolete

我试图得到的结果是

   Item     Text

   12755    this item is no longer for sale
   abc123   please use another code
   xyz987   obsolete

因此，按项目代码"将其分组，并且无论有多少行，文本行都将相加.

So it is grouped by Item code and the text lines are added up regardless of how many there are.

我首先在查询中尝试通过将文件自身连接5次(最多可以有5个文本行)来尝试它，但是我无法使其正常工作.

I tried it in query first by joining the file to itself 5 times (there can be as many as 5 text lines) but i could never get it to work.

我在SQL中进行了尝试，但是我无法获得im familair用来正确处理文本的命令，并且无法在不将文本行重复的情况下将它们添加在一起的标准.

I tried it in SQL but i cant get the commands im familair with to work correctly with text and im struggling on the criteria to add them together without duplicating the text lines up.

任何人都知道吗?

我的最后一招是将该文件自身提取5次，每个文件仅包含一个不同的文本行，然后根据项目将它们重新匹配在一起，并以这种方式进行串联...但是哇，那真是太乱了:)

My last resort is to extract this file 5 times over itself with each file just containing a different text line and then matching them all back together based on item and concatenating that way... but wow how messy that is :)

请帮助:)

推荐答案

我的DB2可以做到这一点.

DB2 for i can do this.

也许最简洁的方法是使用其他DB2平台所没有的经常被忽视的功能-一种称为

Perhaps the most concise way is to use an often overlooked feature, that the other DB2 platforms do not have -- a type of recursive query known as a hierarchical query. Like many things, it's fairly simple once you understand it, but it deserves a bit of explaining to understand how it works.

递归或分层查询可帮助您将一行连接到一个或多个其他行.在这种情况下，我们将建立一对一连接的链.

A recursive or hierarchical query helps you connect one row to one or more others. In this case we are making a chain of one to one connections.

SYS_CONNECT_BY_PATH函数沿着从开始(或根")行到结尾(或叶")行的路径连接字符串.

The aggregate SYS_CONNECT_BY_PATH function will concatenate strings along the path from a starting (or "root") row to the end (or "leaf") row.

首先将示例数据放入表格中.

Let's start by putting your sample data into a table.

declare global temporary table snippets
( item    varchar(10)
, seq     smallint
, words   varchar(30)
);

insert into snippets  
  values 
   ('12755',  1, 'this item')
  ,('12755',  2, 'is no longer')
  ,('12755',  3, 'for sale')
  ,('abc123', 1, 'please use')
  ,('abc123', 2, 'another code')
  ,('xyz987', 1, 'obsolete')
;

让我们看一下我们将用作第一阶段构建块的分层查询.

Let's look at the hierarchical query we will use as a first phase building block.

 SELECT item
      , seq
      , CONNECT_BY_ISLEAF as flag
      , SYS_CONNECT_BY_PATH(words, ' ') as phrase
   FROM snippets
   START WITH seq = 1
   CONNECT BY PRIOR item = item and PRIOR (seq + 1) = seq

CONNECT BY 子句定义从表中的一行到另一行的连接条件.

The CONNECT BY clause defines the join conditions from one row in the table to another.

START WITH 子句指定从哪个行开始加入，即根"行.因此，我们将从seq = 1的行开始，然后连接到下一个seq值(seq + 1).该行将连接到以下seq值，依此类推.

The START WITH clause specifies which row(s) to begin joining from, the "root" row. So we are going to start with rows where seq=1, and join to the next seq value (seq+1). That row will join to the following seq value, and so on.

CONNECT_BY_ISLEAF 将指示我们何时在链的最后一行.您一会儿就会知道为什么在这里使用它.

CONNECT_BY_ISLEAF will indicate when we are at the last row in the chain. You'll in a moment why I used it here.

以下是结果:

    ITEM   SEQ  FLAG  PHRASE

    12755   1    0    this item
    12755   2    0    this item is no longer
    12755   3    1    this item is no longer for sale
    abc123  1    0    please use
    abc123  2    1    please use another code
    xyz987  1    1    obsolete

现在，我们所需要做的就是将它放在

Now all we need to do is put that in a common table expression, from which we can take only the rows we want. The "leaf" rows at the end of the chain have the completed string, so those are the ones to pick.

with q as
(
  SELECT item
       , seq
       , CONNECT_BY_ISLEAF as flag
       , SYS_CONNECT_BY_PATH(words, ' ') as phrase
    FROM snippets
    START WITH seq = 1
    CONNECT BY PRIOR item = item and PRIOR (seq + 1) = seq
)
SELECT item
     , phrase
  FROM q
  WHERE flag = 1
  ORDER BY item
;

哪个给了我们

    ITEM     PHRASE

    abc123   please use another code    
    xyz987   obsolete   
    12755    this item is no longer for sale    

那里有.

在其他DB2平台上的人们将不得不寻找另一种解决方案，例如 funkworm 的注释中的常规递归查询以上.

Folks on other DB2 platforms will have to find another solution, such as a regular recursive query in funkworm's comment above.

这篇关于SQL连接同一文件中不同记录的文本字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！