自定义 聚集函数

Question

以前，N_chow写过一段这样的代码，就是用逗号分隔的。一剑飘香，你怎么自己给忘了。

Answer 1

这个问题很有意义,各位高手都来讨论讨论类似于SUM()这样的聚集函数是怎么实现的.

Answer 2

關鍵在於，像SUM這樣的函數，要用在GROUP BY中時，所有一切的條件都是不固定的，包括table名字，哪些欄位是被GROUP BY的等等，而且UDF中不可以含Dynamic SQL，在MSSQL中，我是覺得不太可能實現。除非查到相關的資料，否則真無從下手。

Answer 3

以前，N_chow写过一段这样的代码，就是用逗号分隔的。一剑飘香，你怎么自己给忘了。

Answer 4

現在講的是真正的聚集函数；可以用在任何地方，像SUM等函數一樣。
我上次寫的只是一個死的，只能解決一部份的問題。我後來思考過，也想寫成聚集函数，但苦想N久，最終還是毫無頭緒。：（

Answer 5

現在講的是真正的聚集函数；可以用在任何地方，像SUM等函數一樣。
我上次寫的只是一個死的，只能解決一部份的問題。我後來思考過，也想寫成聚集函数，但苦想N久，最終還是毫無頭緒。：（

Answer 6

現在講的是真正的聚集函数；可以用在任何地方，像SUM等函數一樣。
我上次寫的只是一個死的，只能解決一部份的問題。我後來思考過，也想寫成聚集函数，但苦想N久，最終還是毫無頭緒。：（

Answer 7

我也认为这基本上是不可能的。
SUM()、AVG()之类的统计函数是数据库引擎的一部分，在一句SQL进行语法分析和执行路径选择的时候，这些函数都会被数据库处理。而且对数字类型进行SUM()或者对数字或者字符类型进行MAX()、MIN()都是有实际使用价值的，而对字符类型进行连接我看不出有任何意义：关系型数据库的基本理论根据是集合论，所以在一个表中间任意两行之间不存在依赖关系、也没有先后次序之分；数据类型可以算总和是因为数字相加不依赖于先后次序（1+2和2+1都是等于3），而字符连接就不一样：先后次序不一样结果完全不同（'1' || '2'='12'而'2' || '1'='21')。把两个没有关系的字符串串接不知道有什么通用的意义。
就目前为止，没看到有任何RDBMS引擎提供这种功能，在可预见的将来，也不会有开发商提供这种功能。今天遇到了两个这样的贴子，不会是同一个人吧？
http://www.csdn.net/expert/topic/861/861204.xml?temp=.1151697

Answer 8

以下是來自MS News group上一位牛人Joe Celko 回答的一個相類的問題。You did not bother to do a search, did you? This is an old problem which
gets posted a few times a month.  The LIST([DISTINCT] <string expression>) is part of Sybase's SQL
Anywhere (formerly WATCOM SQL).  It is the only aggregate function to
work on character strings.  It takes a column of strings, removes the
NULLs and merges them into a single result string having commas between
each of the original strings.  The DISTINCT option removes duplicates as
well as NULLs before concatenating the strings together.  This function
is a generalized version of concatenation, just as SUM() is a
generalized version of addition.  This is handy when you use SQL to write SQL queries.  As one simple
example, you can apply it against the schema tables and obtain the names
of all the columns in a table, then use that list to expand a SELECT *
into the current column list.  One way of doing this query without the WATCOM extension is with scalar
subquery expressions.  Assume we have these two tables:CREATE TABLE People
(id INTEGER NOT NULL PRIMARY KEY,
 name CHAR(10) NOT NULL); INSERT INTO People VALUES (1, 'John');
 INSERT INTO People VALUES (2, 'Mary');
 INSERT INTO People VALUES (3, 'Fred');
 INSERT INTO People VALUES (4, 'Jane');CREATE TABLE Clothes
(id INTEGER NOT NULL,
 seq INTEGER NOT NULL,
 item CHAR(10) NOT NULL,
 PRIMARY KEY (id, seq)); INSERT INTO Clothes VALUES (1, 1, 'Hat');
 INSERT INTO Clothes VALUES (1, 2, 'Coat');
 INSERT INTO Clothes VALUES (1, 3, 'Glove');
 INSERT INTO Clothes VALUES (2, 1, 'Hat');
 INSERT INTO Clothes VALUES (2, 2, 'Coat');
 INSERT INTO Clothes VALUES (3, 1, 'Shoes');
 INSERT INTO Clothes VALUES (4, 1, 'Pants');
 INSERT INTO Clothes VALUES (4, 2, 'Socks');Using the LIST() function, we could get an output of the outfits of the
people with the simple query: SELECT P0.id, P0.name, LIST(item) AS fashion
   FROM People AS P0, Clothes AS C0
  WHERE P0.id = C0.id
  GROUP BY P0.id, P0.name;Result
id  name    fashion 
=======================
 1  'John'  'Hat,Glove'
 2  'Mary'  'Hat,Coat'
 4  'Jane'  'Socks'One way to do this without an aggregate function, requires you must
first know the highest sequence number, so you can create the query.
This is a a simple "SELECT MAX(seq) FROM Clothes" statement in this
case, but you might have to use a COUNT(*) for other tables.     SELECT DISTINCT P0.id, P0.name,
        (SELECT COALESCE(item + ',', '')
          FROM Clothes AS C1
          WHERE C1.id = C0.id
            AND C1.seq = 1)  
        + 
         ...
        + (SELECT COALESCE(item + ',', '')
             FROM Clothes AS C1
             WHERE C1.id = C0.id
               AND C1.seq = {n})     FROM People AS P0, Clothes AS C0
  WHERE P0.id = C0.id;-----------------------------------

Answer 9

以下是來自MS News group上一位牛人Joe Celko 回答的一個相類的問題。You did not bother to do a search, did you? This is an old problem which
gets posted a few times a month.  The LIST([DISTINCT] <string expression>) is part of Sybase's SQL
Anywhere (formerly WATCOM SQL).  It is the only aggregate function to
work on character strings.  It takes a column of strings, removes the
NULLs and merges them into a single result string having commas between
each of the original strings.  The DISTINCT option removes duplicates as
well as NULLs before concatenating the strings together.  This function
is a generalized version of concatenation, just as SUM() is a
generalized version of addition.  This is handy when you use SQL to write SQL queries.  As one simple
example, you can apply it against the schema tables and obtain the names
of all the columns in a table, then use that list to expand a SELECT *
into the current column list.  One way of doing this query without the WATCOM extension is with scalar
subquery expressions.  Assume we have these two tables:CREATE TABLE People
(id INTEGER NOT NULL PRIMARY KEY,
 name CHAR(10) NOT NULL); INSERT INTO People VALUES (1, 'John');
 INSERT INTO People VALUES (2, 'Mary');
 INSERT INTO People VALUES (3, 'Fred');
 INSERT INTO People VALUES (4, 'Jane');CREATE TABLE Clothes
(id INTEGER NOT NULL,
 seq INTEGER NOT NULL,
 item CHAR(10) NOT NULL,
 PRIMARY KEY (id, seq)); INSERT INTO Clothes VALUES (1, 1, 'Hat');
 INSERT INTO Clothes VALUES (1, 2, 'Coat');
 INSERT INTO Clothes VALUES (1, 3, 'Glove');
 INSERT INTO Clothes VALUES (2, 1, 'Hat');
 INSERT INTO Clothes VALUES (2, 2, 'Coat');
 INSERT INTO Clothes VALUES (3, 1, 'Shoes');
 INSERT INTO Clothes VALUES (4, 1, 'Pants');
 INSERT INTO Clothes VALUES (4, 2, 'Socks');Using the LIST() function, we could get an output of the outfits of the
people with the simple query: SELECT P0.id, P0.name, LIST(item) AS fashion
   FROM People AS P0, Clothes AS C0
  WHERE P0.id = C0.id
  GROUP BY P0.id, P0.name;Result
id  name    fashion 
=======================
 1  'John'  'Hat,Glove'
 2  'Mary'  'Hat,Coat'
 4  'Jane'  'Socks'One way to do this without an aggregate function, requires you must
first know the highest sequence number, so you can create the query.
This is a a simple "SELECT MAX(seq) FROM Clothes" statement in this
case, but you might have to use a COUNT(*) for other tables.     SELECT DISTINCT P0.id, P0.name,
        (SELECT COALESCE(item + ',', '')
          FROM Clothes AS C1
          WHERE C1.id = C0.id
            AND C1.seq = 1)  
        + 
         ...
        + (SELECT COALESCE(item + ',', '')
             FROM Clothes AS C1
             WHERE C1.id = C0.id
               AND C1.seq = {n})     FROM People AS P0, Clothes AS C0
  WHERE P0.id = C0.id;-----------------------------------

Answer 10

以下是來自MS News group上一位牛人Joe Celko 回答的一個相類的問題。You did not bother to do a search, did you? This is an old problem which
gets posted a few times a month.  The LIST([DISTINCT] <string expression>) is part of Sybase's SQL
Anywhere (formerly WATCOM SQL).  It is the only aggregate function to
work on character strings.  It takes a column of strings, removes the
NULLs and merges them into a single result string having commas between
each of the original strings.  The DISTINCT option removes duplicates as
well as NULLs before concatenating the strings together.  This function
is a generalized version of concatenation, just as SUM() is a
generalized version of addition.  This is handy when you use SQL to write SQL queries.  As one simple
example, you can apply it against the schema tables and obtain the names
of all the columns in a table, then use that list to expand a SELECT *
into the current column list.  One way of doing this query without the WATCOM extension is with scalar
subquery expressions.  Assume we have these two tables:CREATE TABLE People
(id INTEGER NOT NULL PRIMARY KEY,
 name CHAR(10) NOT NULL); INSERT INTO People VALUES (1, 'John');
 INSERT INTO People VALUES (2, 'Mary');
 INSERT INTO People VALUES (3, 'Fred');
 INSERT INTO People VALUES (4, 'Jane');CREATE TABLE Clothes
(id INTEGER NOT NULL,
 seq INTEGER NOT NULL,
 item CHAR(10) NOT NULL,
 PRIMARY KEY (id, seq)); INSERT INTO Clothes VALUES (1, 1, 'Hat');
 INSERT INTO Clothes VALUES (1, 2, 'Coat');
 INSERT INTO Clothes VALUES (1, 3, 'Glove');
 INSERT INTO Clothes VALUES (2, 1, 'Hat');
 INSERT INTO Clothes VALUES (2, 2, 'Coat');
 INSERT INTO Clothes VALUES (3, 1, 'Shoes');
 INSERT INTO Clothes VALUES (4, 1, 'Pants');
 INSERT INTO Clothes VALUES (4, 2, 'Socks');Using the LIST() function, we could get an output of the outfits of the
people with the simple query: SELECT P0.id, P0.name, LIST(item) AS fashion
   FROM People AS P0, Clothes AS C0
  WHERE P0.id = C0.id
  GROUP BY P0.id, P0.name;Result
id  name    fashion 
=======================
 1  'John'  'Hat,Glove'
 2  'Mary'  'Hat,Coat'
 4  'Jane'  'Socks'One way to do this without an aggregate function, requires you must
first know the highest sequence number, so you can create the query.
This is a a simple "SELECT MAX(seq) FROM Clothes" statement in this
case, but you might have to use a COUNT(*) for other tables.     SELECT DISTINCT P0.id, P0.name,
        (SELECT COALESCE(item + ',', '')
          FROM Clothes AS C1
          WHERE C1.id = C0.id
            AND C1.seq = 1)  
        + 
         ...
        + (SELECT COALESCE(item + ',', '')
             FROM Clothes AS C1
             WHERE C1.id = C0.id
               AND C1.seq = {n})     FROM People AS P0, Clothes AS C0
  WHERE P0.id = C0.id;-----------------------------------