本文演示了如何使用不同的 SQL 数据库将多个字符串值连接成一个字符串。 目标是聚合一个字符串字段。
Oracle 和 DB2:
Oracle 和 DB2 都提供内置的 LISTAGG
函数。 语法很简单:
<code class="language-sql">SELECT FieldA, LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;</code>
PostgreSQL:
PostgreSQL 使用 STRING_AGG
函数:
<code class="language-sql">SELECT FieldA, STRING_AGG(FieldB, ',') AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;</code>
SQL Server:
SQL Server 2017 及更高版本也支持STRING_AGG
。 对于旧版本(2017 年之前),需要使用 STUFF
和 FOR XML PATH
的更复杂方法:
<code class="language-sql">WITH CTE_TableName AS ( SELECT FieldA, FieldB FROM TableName ) SELECT t0.FieldA, STUFF(( SELECT ',' + t1.FieldB FROM CTE_TableName t1 WHERE t1.FieldA = t0.FieldA ORDER BY t1.FieldB FOR XML PATH('') ), 1, LEN(','), '') AS FieldBs FROM CTE_TableName t0 GROUP BY t0.FieldA ORDER BY FieldA;</code>
SQLite:
SQLite的GROUP_CONCAT
函数可以进行字符串聚合。 订购需要 CTE 或子查询:
订购时:
<code class="language-sql">WITH CTE_TableName AS ( SELECT FieldA, FieldB FROM TableName ORDER BY FieldA, FieldB ) SELECT FieldA, GROUP_CONCAT(FieldB, ',') AS FieldBs FROM CTE_TableName GROUP BY FieldA ORDER BY FieldA;</code>
未订购:
<code class="language-sql">SELECT FieldA, GROUP_CONCAT(FieldB, ',') AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;</code>
以上是不同的SQL数据库如何实现字符串聚合(LISTAGG)?的详细内容。更多信息请关注PHP中文网其他相关文章!