第七章 SQL聚合函数 LIST

2,405 阅读5分钟

第七章 SQL聚合函数 LIST

创建逗号分隔值列表的聚合函数。

大纲

LIST([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])

参数

  • ALL - 可选-指定LIST返回string-expr的所有值的列表。 如果没有指定关键字,这是默认值。
  • DISTINCT - 可选-一个DISTINCT子句,指定LIST返回一个仅包含唯一的string-expr值的列表。 DISTINCT可以指定BY(colo -list)子句,其中colo -list可以是单个字段,也可以是用逗号分隔的字段列表。
  • string-expr - 计算结果为字符串的SQL表达式。 通常是所选表中列的名称。
  • %FOREACH(col-list) - 可选-列名或以逗号分隔的列名列表。
  • %AFTERHAVING - 可选-应用在HAVING子句中的条件。

描述

LIST聚合函数返回指定列中以逗号分隔的值列表。

一个简单的LIST(或LIST ALL)返回一个字符串,其中包含一个逗号分隔的列表,该列表由所选行中string-expr的所有值组成。 其中string-expr为空字符串(")的行由逗号分隔列表中的占位符逗号表示。 string-exprNULL的行不包含在逗号分隔的列表中。 如果只有一个string-expr值,并且是空字符串("),LIST返回空字符串。

LIST DISTINCT返回一个字符串,该字符串包含一个逗号分隔的列表,该列表由所选行中string-expr的所有不同(唯一)值组成:LIST(DISTINCT col1)NULL string-expr不包含在逗号分隔的列表中。 LIST(DISTINCT BY(col2) col1)返回一个逗号分隔的列表,其中只包含那些col2值是不同(唯一的)的记录中的col1字段值。 但是请注意,不同的col2值可能包含一个单独的NULL值。

包含逗号的数据值

因为LIST使用逗号分隔string-expr值,所以LIST不应该用于包含逗号的数据值。 使用%DLISTJSON_ARRAYAGG代替。

LIST 和 %SelectMode

可以使用%SelectMode属性来指定LIST返回的数据显示模式:0=Logical(默认),1=ODBC, 2= display

注意,LIST用逗号分隔列值,而ODBC模式用逗号分隔%LIST列值中的元素。 因此,在%LIST结构上使用LIST时,使用ODBC模式会产生不明确的结果。

LIST 和 ORDER BY

LIST函数将多个行中的一个表列的值组合成一个逗号分隔的值列表。 因为在计算所有聚合字段之后,查询结果集中应用了一个ORDER BY子句,所以ORDER BY不能直接影响这个列表中的值序列。 在某些情况下,LIST结果可能会按顺序出现,但是不应该依赖这种顺序。 在给定聚合结果值中列出的值不能显式排序。

最大列表大小

允许的最大LIST返回值是最大字符串长度,3641,144个字符。

相关的聚合函数

  • LIST返回一个逗号分隔的值列表。
  • %DLIST返回一个包含每个值的元素的列表。
  • JSON_ARRAYAGG返回值的JSON数组。
  • XMLAGG返回一个串接的值字符串。

示例

下面的嵌入式SQL示例返回一个主机变量,该变量包含示例的Home_State列中列出的所有值的逗号分隔列表。 以字母A开头的人名表:

ClassMethod ListFunction()
{
	&sql(SELECT LIST(Home_State)
		INTO :statelist
		FROM Sample.Person
		WHERE Home_State %STARTSWITH 'A')
	w "The states are:",!,statelist
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).ListFunction()
The states are:
AR,AL,AZ,AZ,AZ,AR,AL,AZ,AR,AL,AL,AR

注意,这个列表包含重复的值。

下面的嵌入式SQL示例返回一个主机变量,该变量包含示例的Home_State列中列出的所有不同(唯一)值的逗号分隔列表。 以字母A开头的人名表:

ClassMethod ListFunction1()
{
	&sql(SELECT LIST(DISTINCT Home_State)
		INTO :statelist
		FROM Sample.Person
		WHERE Home_State %STARTSWITH 'A')
	w "The distinct states are:",!,statelist
}
DHC-APP> d ##class(PHA.TEST.SQLCommand).ListFunction1()
The distinct states are:
AR,AL,AZ

下面的SQL示例为每个州创建了一个逗号分隔的列表,其中包含在Home_City列中找到的所有值,以及按州列出的这些城市值的计数。 每个Home_State行包含该状态的所有Home_City值的列表。 这些名单可能包括重复的城市名称:

SELECT Home_State,
       COUNT(Home_City) AS CityCount,
       LIST(Home_City) AS ListAllCities
FROM Sample.Person
GROUP BY Home_State

image.png

也许更有用的方法是在Home_City列中列出每个州的不同值,用逗号分隔,如下所示:

SELECT Home_State,
       COUNT(DISTINCT Home_City) AS DistCityCount,
       COUNT(Home_City) AS TotCityCount,
       LIST(DISTINCT Home_City) AS DistCitiesList
FROM Sample.Person
GROUP BY Home_State

image.png

注意,这个示例返回每个州的不同城市名称和总城市名称的整数计数。

下面的示例返回以“A”开头的Home_State值列表。 它返回不同的Home_State值(distinct Home_State); 与不同的Home_City值(distinct BY(Home_City) Home_State)相对应的Home_State值,这可能包括一个唯一的Home_City NULL; 和所有Home_State值:

SELECT LIST(DISTINCT Home_State) AS DistStates,
       LIST(DISTINCT BY(Home_City) Home_State) AS DistCityStates,
       LIST(Home_State) AS AllStates
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'   

image.png

下面的动态SQL示例使用%SelectMode属性为DOB日期字段返回的值列表指定ODBC显示模式:

ClassMethod ListFunction2()
{
	s myquery = "SELECT LIST(DOB) AS DOBs FROM Sample.Person WHERE Name %STARTSWITH 'A'"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=1
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	d rset.%Display()
	w !,"End of data"
}
DHC-APP>d ##class(PHA.TEST.SQLCommand).ListFunction2()
DOBs
1973-06-17,1943-05-22,1943-04-21,2011-07-07,1993-10-05,1953-02-25,1968-11-18
 
1 Rows(s) Affected
End of data

下面的动态SQL示例使用%FOREACH关键字。 它为每个不同的Home_State返回一行,其中包含该Home_State的年龄值列表:

ClassMethod ListFunction3()
{
	s myquery = 3
	s myquery(1) = "SELECT DISTINCT Home_State,"
	s myquery(2) = "LIST(Age %FOREACH(Home_State)) AgesForState "
	s myquery(3) = "FROM Sample.Person WHERE Home_State %STARTSWITH 'M'"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=1
	s qStatus = tStatement.%Prepare(.myquery)
	if qStatus'=1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	d rset.%Display()
	w !,"End of data"
}
DHC-APP>d ##class(PHA.TEST.SQLCommand).ListFunction3()
Home_State      AgesForState
MA      55
MD      10,36,31,85,10,94,28
ME      25,20,55,11
MI      11,75,46,17,71
MN      11,43,73,98
MO      77,33,42,55,89,26,87,45,34
MS      7,25,13
MT      4,35,54,6,23
 
8 Rows(s) Affected
End of data

下面的示例使用了%AFTERHAVING关键字。 它为每个Home_State返回一行,其中至少包含一个满足HAVING子句条件的Name值(以“M”开头的名称)。 第一个LIST函数返回该状态的所有名称的列表。 第二个LIST函数返回的列表只包含满足HAVING子句条件的名称:

SELECT Home_State,
       LIST(Name) AS AllNames,
       LIST(Name %AFTERHAVING) AS HavingClauseNames
    FROM Sample.Person
    GROUP BY Home_State
    HAVING Name LIKE 'M%'
    ORDER BY Home_State

image.png