Informix中的group_concat [英] group_concat in Informix

问题描述

在Informix的SQL中查找可模拟MySQL的group_concat函数的查询.

Looking for a query in Informix's SQL that will simulate MySQL's group_concat function.

MySQL的group_concat所做的是创建该组中所有成员的枚举.

What MySQL's group_concat does is it creates an enumeration of all members in the group.

因此，数据如下:

orderid:itemName:price
      1:Paper   :10
      1:Pen     :5
      2:Sugar   :15

以及以下查询:

select group_concat(itemName), sum(price)
from order_details
group by orderid

会产生:

items    :price
Paper,Pen:15
Sugar    :15

在Informix中实现这一目标的最有效方法是什么?我们一定要使用存储过程吗?

What would be most efficient way to achieve this in Informix? Would we definitely have to use a stored procedure?

推荐答案

您必须定义用户定义的集合才能执行此操作.那有四个部分-四个功能(搜索

You would have to define a user-defined aggregate to do this. That has four parts - four functions (search for CREATE AGGREGATE in the IDS 12.10 Info Centre):

1. 初始化器(INIT)
2. 迭代器(ITER)
3. 组合(combine)
4. 最终化器(最终)

这是首都的官方术语，相当直观.考虑计算平均值.

That's the official terminology in capitals, and it is moderately intuitive. Think of calculating an average.

1. 初始化器:设置总和= 0； N = 0
2. 迭代器:设置总和+ = x; N ++
3. 组合器:设置总和= sum1 + sum2;设置N = N1 + N2
4. 最终化器:结果= sum/N-N = 0(零分频)检查

合并器用于合并并行执行的中间结果；每个并行执行都从迭代器开始，并生成中间结果.并行执行完成后，将单独的值集与合并器合并.

The combiner is used to combine intermediate results from parallel execution; each parallel execution starts with the iterator and generates intermediate results. When the parallel execution completes, the separate sets of values are combined with the combiner.

您可以使用存储过程或C或Java UDR在IDS中编写类似的代码.

You can write analogous code in IDS - using stored procedures or C or Java UDRs.

请参阅SO问题

See the SO question Show a one to many relationship as 2 columns — 1 unique row (ID & comma separated list) for a string-based GROUP_CONCAT() function implemented in Informix.

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