用户定义的自定义聚合函数，使用plsql [英] user defined custom aggregate function using plsql

问题描述

我有问题.我使用的工具不接受子查询.因此，我必须编写一个执行以下操作的用户定义的自定义聚合函数.

I have a problem.The tool i work on does not accept subqueries. So i have to write a user defined custom aggregate function that does the following.

示例:col1和col2具有数字数据类型.

Example: col1 and col2 has numeric datatype.

**col1**    **col2**
243         401
57489       400
2789        401
598         400

SELECT sum(MinValue) 
FROM
    (SELECT Min(col1) AS MinValue,col2
    FROM
    Table1
    GROUP BY col2)

Output:841

我希望此查询通过使用单个函数而不是上面显示的查询在单个步骤中工作.像这样:

I want this query to work in a single step by using a single function rather than as the query shown above. Something like:

SELECT MyMinSum(col1, col2),col3 from table1 Group by col3;

我希望这是有道理的.我将不胜感激.

I hope it makes sense. I will appreciate any input on this.

推荐答案

如果您希望通过子查询使用select语句查找相同的输出，则可以通过

If you are looking for same output what you are getting with your select statement using sub query, you could achieve the same result by

         SELECT   SUM (MIN (col1)) AS minvalue
 FROM   Table1
GROUP BY   col2

这篇关于用户定义的自定义聚合函数，使用plsql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！