与SQL中的STUFF等效的功能(MySSQL中的GROUP_CONCAT/Oracle中的LISTAGG) [英] Equivalent function to STUFF in SQL (GROUP_CONCAT in MySSQL / LISTAGG in Oracle)

问题描述

有人知道Firebird 2.5是否具有类似于"STUFF"功能的功能.在SQL中起作用?

Does anyone know if Firebird 2.5 has a function similar to the "STUFF" function in SQL?

我有一个表，其中包含父用户记录，而另一个表中包含与父用户相关的子用户记录.我希望能够以逗号分隔字符串"ROLES".用户不必使用第二个查询，就可以循环返回给定ID的值并自己创建字符串.

I have a table which contains parent user records, and another table which contains child user records related to the parent. I'd like to be able to pull a comma delimited string of the "ROLES" the user has without having to use a second query, loop over the values returned for the given ID and create the string myself.

我已经搜索了其他任何相关问题，但没有找到任何问题. 此链接中的问题要连接的Sum等效字符串基本上就是我想要的也可以，但要使用Firebird 2.5数据库.

I've searched for any other related questions, but have not found any. The question in this link string equivalent of Sum to concatenate is basically what I want to do too, but with the Firebird 2.5 database.

推荐答案

您似乎很幸运-Firebird 2.1引入了

It looks like you are in luck - Firebird 2.1 introduced a LIST() aggregate function which works like GROUP_CONCAT in MySQL, which allows a query like so:

SELECT p.Name, LIST(c.Name, ', ')
FROM parent p INNER JOIN child c on c.parentid = p.parentid
GROUP by p.Name;

编辑，重新订购

在应用LIST聚合函数之前，您可以通过对派生表中的数据进行预排序来影响排序，如下所示:

You may be able to influence ordering by pre-ordering the data in a derived table, prior to applying the LIST aggregation function, like so:

SELECT x.ParentName, LIST(x.ChildName, ', ')
FROM 
(
  SELECT p.Name as ParentName, c.Name as ChildName
  FROM parent p INNER JOIN child c on c.parentid = p.parentid
  ORDER BY c.Name DESC
) x
GROUP by x.ParentName;

这篇关于与SQL中的STUFF等效的功能(MySSQL中的GROUP_CONCAT/Oracle中的LISTAGG)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！