递归 Teradata 查询 [英] Recursive Teradata Query

问题描述

我正在尝试将下表查询到一个合并和排序的列表中，例如:

I'm trying to query the below table into a consolidated and sorted list, such as:

起始名单:

GROUP_ID    MY_RANK EMP_NAME
1   1   Dan
1   2   Bob
1   4   Chris
1   3   Steve
1   5   Cal
2   1   Britt
2   2   Babs
2   3   Beth
3   1   Vlad
3   3   Eric
3   2   Mike

查询结果:

1   Dan, Bob, Steve, Chris, Cal
2   Britt, Babs, Beth
3   Vlad, Mike, Eric

它需要使用递归查询，因为列表要长得多.另外，我必须按 my_rank 排序才能按顺序获取它们.提前致谢.我已经尝试了在不同论坛上找到的大约 10 个示例，但我被卡住了.另外，不要担心截断任何尾随/前导逗号.

It needs to use a recursive query because the list is much longer. Also, I have to sort by my_rank to get them in sequential order. Thanks in advance. I've tried about 10 examples found on different forums, but I'm stuck. Also, don't worry about truncating the any trailing/leading commas.

CREATE TABLE MY_TEST (GROUP_ID INTEGER NOT NULL, MY_RANK INTEGER NOT NULL, EMP_NAME VARCHAR(18) NOT NULL);
INSERT INTO MY_TEST VALUES (1, 1, 'Dan');
INSERT INTO MY_TEST VALUES (1, 2, 'Bob');
INSERT INTO MY_TEST VALUES (1, 4, 'Chris');
INSERT INTO MY_TEST VALUES (1, 3, 'Steve');
INSERT INTO MY_TEST VALUES (1, 5, 'Cal');
INSERT INTO MY_TEST VALUES (2, 1, 'Britt');
INSERT INTO MY_TEST VALUES (2, 2, 'Babs');
INSERT INTO MY_TEST VALUES (2, 3, 'Beth');
INSERT INTO MY_TEST VALUES (3, 1, 'Vlad');
INSERT INTO MY_TEST VALUES (3, 3, 'Eric');
INSERT INTO MY_TEST VALUES (3, 2, 'Mike');

推荐答案

您的 Teradata 版本是什么?是否安装了 XML 服务?

What's your Teradata release? Are XML-Services installed?

SELECT * FROM dbc.FunctionsV
WHERE FunctionName = 'XMLAGG';

如果这个函数存在，你可以避免递归(反正效率不高):

If this function exists you can avoid recursion (which is not very efficient anyway):

SELECT GROUP_ID, 
   TRIM(TRAILING ',' FROM
             CAST(XMLAGG(EMP_NAME || ',' ORDER BY MY_RANK) AS VARCHAR(10000)))
FROM MY_TEST
GROUP BY 1

这篇关于递归 Teradata 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！