sql - DB2 中的 SQL 递归连接问题

Question

我在编写 SQL 方面还比较陌生，似乎无法从我的递归语句中获得正确的输出。源表是这样的:

SEQUENCE    TEXT        CATEGORY
1           This is     apples
2           a complete  apples
3           sentence.   apples

我想要得到的输出是:

CATEGORY    TEXT
apples      This is a complete sentence.

但是，我得到的结果是:

CATEGORY    TEXT
apples      This is a complete

我基本上无法连接超过 2 行。我认为这与我在 WITH 语句的递归部分中设置的计数有关。

WITH rquery (category, sequence, sentence)
         AS (SELECT base.category, base.sequence, base.text
             FROM myTable base
             WHERE sequence = 1
             Union ALL
             SELECT t1.category, t1.sequence, sentence || ' ' || t1.text
             FROM rquery t0, myTable t1
             WHERE t0.category = t1.category And t0.sequence + 1 = t1.sequence
             ) 
SELECT * FROM rquery WHERE sequence in (SELECT MAX(sequence) FROM rquery);

如果您删除最后一个 WHERE 子句，结果会显示发生了什么，但我似乎无法找到正确的解决方案。这里没有最后一个 WHERE 子句:

CATEGORY    SEQUENCE    SENTENCE
apples      1           This is
apples      2           This is a complete
apples      3           This is a complete

Answer 1

根据 TEXT 的定义方式，最终值可能会被截断，然后，根据您用来运行查询的客户端，您可能永远不会知道这一点。

尝试将 TEXT 转换为更长的 VARCHAR，例如

WITH rquery (category, sequence, sentence)
     AS (SELECT base.category, base.sequence, CAST( base.text AS VARCHAR(100))
         FROM myTable base
         ...

sentence 的结果数据类型必须足够大，以适应可能最长的 text 连接。