SYBASE LIST 函数与SQL的合并实现方式

sybase ASA

LIST 函数 [Aggregate]

返回组中每行值的分隔列表。

正在上传…重新上传取消​ 语法

LIST( 
[ALL | DISTINCT ] string-expression
[, delimiter-string ]
[ ORDER BY order-by-expression [ ASC | DESC ], ... ] ) 
[ALL | DISTINCT ] string-expression
[, delimiter-string ]
[ ORDER BY order-by-expression [ ASC | DESC ], ... ] )

正在上传…重新上传取消​ 参数

• string-expression   字符串表达式，通常是列名。如果指定了 ALL（缺省设置），对于组中的每一行，string-expression 的值会添加到结果字符串，并用 delimiter-string 分隔这些值。如果指定了 DISTINCT，则只添加唯一的 string-expression 值。

• delimiter-string   列表项的分隔字符串。缺省设置是逗号。如果设为 NULL 值或空字符串，则没有分隔符。delimiter-string 必须是常量。

• order-by-expression   对该函数返回的项进行排序。此参数之前没有逗号，以便在未提供 delimiter-string 的情况下使用。

  order-by-expression 不能是整数值。但可以是包含整数的变量。

  当 ORDER BY 子句包含常量时，由优化程序对常量进行解释，然后替换为等效的 ORDER BY 子句。例如，优化程序将 ORDER BY 'a' 解释为 ORDER BY 表达式。

  对含多个集合函数的查询块，如果几个有效的 ORDER BY 子句可以在逻辑上组合成一个 ORDER BY 子句，则可执行该 ORDER BY 子句。例如，以下子句：

  ORDER BY expression1, 'a', expression2

  ORDER BY expression1, 'b', expression2, 'c', expression3

  可归入下个子句：

  ORDER BY expression1, expression2, expression3

正在上传…重新上传取消​ 返回值

• LONG VARCHAR
• LONG NVARCHAR

正在上传…重新上传取消​ 注释

LIST 函数返回一串组中每一行 X 的所有非 NULL 值（带分隔符）。如果组中没有任何一行具有明确的 X 值，则 LIST( X ) 返回空字符串。

LIST 函数会忽略 NULL 值和空字符串。

LIST 函数不能用作窗口函数，但可用作窗口函数的输入。

此函数支持 NCHAR 输入和/或输出。

正在上传…重新上传取消​ 标准和兼容性

• SQL/2008   服务商扩充。

  SQL Anywhere 支持 SQL/2008 语言功能 F441 "扩展集函数支持"，它允许集合函数的操作数是列引用以外的任意表达式。

  SQL Anywhere 不支持可选的 SQL/2008 功能 F442 "集函数中的混合列引用"。SQL Anywhere 不允许集合函数参数既含有来自包含 LIST 函数的查询块的列引用，又含有外部引用。请参见集合函数和外部引用。

  有关示例内容，请参见AVG 函数 [Aggregate]。

正在上传…重新上传取消​ 另请参见

• sa_split_list 系统过程

正在上传…重新上传取消​ 示例

以下语句返回值 487 Kennedy Court, 547 School Street。

SELECT LIST( Street ) FROM Employees WHERE GivenName = 'Thomas';

以下语句列出雇员 ID。结果集中的每一行都包含某一部门雇员 ID 的逗号分隔列表。

SELECT LIST( EmployeeID ) FROM Employees GROUP BY DepartmentID;

LIST( EmployeeID )
102,105,160,243,247,249,266,278,...
129,195,299,467,641,667,690,856,...
148,390,586,757,879,1293,1336,...
184,207,318,409,591,888,992,1062,...
191,703,750,868,921,1013,1570,...

以下语句对职员 ID 按职员的姓进行排序：

SELECT LIST( EmployeeID ORDER BY Surname ) AS "Sorted IDs" FROM Employees GROUP BY DepartmentID;

Sorted IDs '1751,591,1062,1191,992,888,318,184,1576,207,1684,1643,1607,1740,409,1507'

Sorted ID
1013,191,750,921,868,1658,...
1751,591,1062,1191,992,888,318,...
1336,879,586,390,757,148,1483,...
1039,129,1142,195,667,1162,902,...
160,105,1250,247,266,249,445,...

以下语句返回以分号分隔的列表。请注意 ORDER BY 子句的位置和列表分隔符：

SELECT LIST( EmployeeID, ';' ORDER BY Surname ) AS "Sorted IDs" FROM Employees GROUP BY DepartmentID;

Sorted ID
1013;191;750;921;868;1658;703;...
1751;591;1062;1191;992;888;318;...
1336;879;586;390;757;148;1483;...
1039;129;1142;195;667;1162;902; ...
160;105;1250;247;266;249;445;...

务必要将以下语句与前一个语句加以区分，以下语句返回以逗号分隔并按复合排序关键字 ( Surname, ';' ) 排序的雇员 ID 列表：

SELECT LIST( EmployeeID ORDER BY Surname, ';' ) AS "Sorted IDs" FROM Employees GROUP BY DepartmentID;

---

SQL:

合并列值 

--*******************************************************************************************

表结构，数据如下： 

id    value 

----- ------ 

1    aa 

1    bb 

2    aaa 

2    bbb 

2    ccc 

需要得到结果： 

id     values 

------ ----------- 

1      aa,bb 

2      aaa,bbb,ccc 

即： group  by  id, 求 value 的和（字符串相加） 

1. 旧的解决方法(在sql server 2000中只能用函数解决。) 

--=============================================================================

create  table  tb(id  int , value  varchar (10)) 

insert  into  tb  values (1,  'aa' ) 

insert  into  tb  values (1,  'bb' ) 

insert  into  tb  values (2,  'aaa' ) 

insert  into  tb  values (2,  'bbb' ) 

insert  into  tb  values (2,  'ccc' ) 

go 

--1. 创建处理函数 

CREATE  FUNCTION  dbo.f_strUnite(@id  int ) 

RETURNS  varchar (8000) 

AS 

BEGIN 

     DECLARE  @str  varchar (8000) 

     SET  @str =  '' 

     SELECT  @str = @str +  ','  + value  FROM  tb  WHERE  id=@id 

     RETURN  STUFF(@str, 1, 1,  '' ) 

END 

GO 

-- 调用函数 

SELECt  id, value = dbo.f_strUnite(id)  FROM  tb  GROUP  BY  id 

drop  table  tb 

drop  function  dbo.f_strUnite 

go

/* 

id          value      

----------- ----------- 

1          aa,bb 

2          aaa,bbb,ccc 

（所影响的行数为 2 行） 

*/ 

--===================================================================================

2. 新的解决方法(在sql server 2005中用 OUTER  APPLY等解决。) 

create  table  tb(id  int , value  varchar (10)) 

insert  into  tb  values (1,  'aa' ) 

insert  into  tb  values (1,  'bb' ) 

insert  into  tb  values (2,  'aaa' ) 

insert  into  tb  values (2,  'bbb' ) 

insert  into  tb  values (2,  'ccc' ) 

go 

-- 查询处理 

SELECT  *  FROM ( SELECT  DISTINCT  id  FROM  tb)A  OUTER  APPLY( 

         SELECT  [ values ]= STUFF( REPLACE ( REPLACE ( 

             ( 

                 SELECT  value  FROM  tb N 

                 WHERE  id = A.id 

                 FOR  XML AUTO 

             ),  ' <N value="' ,  ',' ),  '"/>' ,  '' ), 1, 1,  '' ) 

)N 

drop  table  tb 

/* 

id           values 

----------- ----------- 

1          aa,bb 

2          aaa,bbb,ccc 

(2 行受影响) 

*/ 

--SQL2005中的方法2 

create  table  tb(id  int , value  varchar (10)) 

insert  into  tb  values (1,  'aa' ) 

insert  into  tb  values (1,  'bb' ) 

insert  into  tb  values (2,  'aaa' ) 

insert  into  tb  values (2,  'bbb' ) 

insert  into  tb  values (2,  'ccc' ) 

go 

select  id, [ values ]=stuff(( select  ',' +[value]  from  tb t  where  id=tb.id  for  xml path( '' )), 1, 1,  '' ) 

from  tb 

group  by  id 

/* 

id           values 

----------- -------------------- 

1          aa,bb 

2          aaa,bbb,ccc 

(2 row(s) affected) 

*/ 

drop  table  tb 

_________________________________________

SQL:

current table; 
    QUESTION_ID ANSWER_ID USER 
1.  1    1  A 
2.  1    1  B 
3.  1    2  C 

i need; 

    QUESTION_ID ANSWER_ID USER 
1.  1    1  A, B 
2.  1    2  C 

select distinct t1.QUESTION_ID, t1.ANSWER_ID 
    STUFF((SELECT distinct '' + t2.USER 
     from yourtable t2 
     where t1.ANSWER_ID= t2.ANSWER_ID 
      FOR XML PATH(''), TYPE 
      ).value('.', 'NVARCHAR(MAX)') 
     ,1,0,'') data 
from yourtable t1;