跳到主要内容
  1. Skills/
  2. thinking in SQL | 废话SQL,思维与实践/

在 SQL 中拼接字符串

··字数 2756·6 分钟
有悟方法 sql 技巧

把多行字符串,使用逗号连接起来。有人说,“我会,java 里有 string.join() 方法,可以把字符串数据拼接起来”。也有人会说,“我在 sublime text 里面使用 ctrl+j 就可以把多行拼接起来”。那如果是在SQL里呢,有秘诀吗?

字符串连接
字符串连接

当然,如果是刚接触数据库不久,使用的数据库版本一般较新,会说,“我知道,oracle 有 listsagg 函数,mysql 数据库也有功能一样的函数”。

没错,listagg 是 ISO:2016 标准中的新函数, What’s New in SQL:2016 #listagg 中给出了详细的说明。

LISTAGG,非常好记,aggrete a (string) list,聚合一个字符串列表。

本文不是为比较各数据库实现字符串分组拼接的差异,而是演示各数据库中相似的功能。通用的字符串分组拼接可以实现,缺少内置支持也可以构造出来,以此拓宽思路。

一众分组聚合字符串连接函数 #

除 oracle 11g2 采用 listagg 外,其它数据库的有自己的版本。

函数名 数据库及支持的版本
listagg oracle 11g2+
string_agg postgres 9.0+
group_concat mysql 5.6 +
group_concat sqlite 3

oracle 中的 LISTAGG #

LISTAGG 函数的语法比较别扭,加了一个尾巴 within group(order by ...)用于指定拼接时各字符串的顺序。

LISTAGG(<expr>, <separator>) WITHIN GROUP(ORDER BY )

postgres 中的 STRING_AGG #

STRING_AGG ( expression, separator [order_by_clause] )
order_by_clause: ORDER BY expression1 {ASC | DESC}, [...]

而 postgres,使用的是 STRING_AGG 函数,非常个性,就是不用 listagg,直接告诉用户,本函数就是用来做“字符串 agg”的。函数也没尾巴,不过要小心,排序的 order by 子句在分隔符 separator 之后,两者之间并无逗号(“,”)。

  • 简单示例
psql (14.2)
Type "help" for help.

postgres=# select generate_series(1,3,1);
 generate_series
-----------------
               1
               2
               3
(3 rows)

generate_series 是 postgres 中用于生成序列的表函数(返回值类型为结果集的函数)。

select string_agg(cast(seq as varchar(1)), ',')
     , string_agg(cast(seq as varchar(1)), ',' order by seq desc)
from   generate_series(1,3,1) as seq;

string_agg 只接受字符类型的参数, postgres 此处没有隐式转换类型,故使用 cast 将拼接的数字强制类型转换为 varchar 类型,使用 order by 可指定连接中的元素顺序。结果为:

 string_agg | string_agg
------------+------------
 1,2,3      | 3,2,1
(1 row)

mysql 中的 GROUP_CONCAT #

msyql 使用的是 group_concat 函数,也没有 within group 尾巴。该函数的用法,参数的第一部分由要的拼接的字段组成。缺省排序与分隔符(默认为逗号)。

-- mysql 语法
GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])
  • 简要示例
mysql> select * from (values row(1), row(a), row(2)) as t(seq);
+-----+
| seq |
+-----+
|   1 |
|   a |
|   2 |
+-----+
3 rows in set (0.00 sec)

values row(1) 的用法, 在 mysql 8.0.19 版本引入 。若数据库版本较低的,可以使用 union all 拼数据集。

select group_concat(seq order by seq desc)
     , group_concat(seq order by seq)
from  (values row(1), row('a'), row(2)) as t(seq) ;

GROUP_CONCAT 函数默认不排序,使用 order by 来影响字符串顺序。结果为:

+-------------------------------------+--------------------------------+
| group_concat(seq order by seq desc) | group_concat(seq order by seq) |
+-------------------------------------+--------------------------------+
| a,2,1                               | 1,2,a                          |
+-------------------------------------+--------------------------------+

使用 distinct 关键字,可去除排列重复元素。

select group_concat(distinct seq)
     , group_concat(seq)
from  (values row(1), row('a'), row(2), row(4), row('a'), row(2)) as t(seq);

结果为:

+----------------------------+-------------------+
| group_concat(distinct seq) | group_concat(seq) |
+----------------------------+-------------------+
| 1,2,3,4                    | 1,3,2,4,3,2       |
+----------------------------+-------------------+

sqlite3 中的 GROUP_CONCAT #

aggregate-func ( DISTINCT expr ) filter-clause , *
  • 简单示例
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> .mode markdown
sqlite> select *
   ...> from   (values (1),('b'),(3),(4),('a'));
| column1 |
|---------|
| 1       |
| b       |
| 3       |
| 4       |
| a       |

使用 values 关键字构建一个多行的数据集。

select group_concat(column1)
     , group_concat(distinct column1)
from   (values (1),('b'),(3),(4),('a'),('b'), (1)) ;

函数 group_concat 的用法与 mysql 中的类似。结果为:

| group_concat(column1) | group_concat(distinct column1) |
|-----------------------|--------------------------------|
| 1,b,3,4,a,b,1         | 1,b,3,4,a                      |

不过,sqlite3 的 group_concat 没有排序功能的 order by 子语,为了得到顺序或者倒序的结果,需要使用子查询先进行排序。

select ( select group_concat(distinct seq) 
         from ( select column1 as seq 
                from (values (1),('b'),(4),('a'),('b'),(1)) order by 1 ))
       as concat_1
     , ( select group_concat(distinct seq) 
         from ( select column1 as seq 
                from (values (1),('b'),(3),('a'),('b'),(1)) order by 1 desc))
       as concat_2 ;

结果为:

| concat_1 | concat_2 |
|----------|----------|
| 1,4,a,b  | b,a,3,1  |

没有内置分组聚合函数的时代 #

本小节内容才是本文的 重点❗️。

早在有悟开始玩SQL的时候,数据库产品的内置函数已经相当丰富,但像字符串拼接函数(2016年才加入SQL标准)这种使用场景较少的,还是缺了。

使用递归查询的方式 #

字符串列表的拼接,其实是对每一行数据(字符类型)进行累积计算(拼接),SQL逐行累积计算,正好可用递归循环的实现。有悟专门介绍过 《递归查询》 ,让我们试试看使用 recursive 查询能否做到?

将四行数据变成两行:

title  name        title  names  
-----  -----   ->  -----  -----
a      youwu       a      today.youwu
a      today       b      今日.有悟
b      今日         
b      有悟         
-- coded in sqlite3                              
with recursive
    seq(title, name) as
    ( values ('a', 'youwu'), ('a', 'today') , ('b', '今日'), ('b', '有悟') )
    , seq_ordered(title, name, row_no) as
    ( select title, name, ROW_NUMBER() OVER( partition by title order by name)
      from seq
    )
    , lop(title, names, row_no) as
    ( select title, name, row_no
      from   seq_ordered
        where  row_no = 1
        union  all
        select lop.title, lop.names || '.' || seq.name, seq.row_no
        from   lop, seq_ordered seq
        where  lop.title = seq.title
        and    lop.row_no + 1 = seq.row_no
    )
    , selected(title, names, select_no1) as
    ( select title, names, ROW_NUMBER() over(partition by title order by row_no desc)
      from   lop
    )
select title, names
from selected
where select_no1 = 1

结果为:

| title |    names    |
|-------|-------------|
| a     | today.youwu |
| b     | 今日.有悟     |

how is it possible? 🤔

首先,构建数据集,

with 
    seq(title, name) as
    ( values ('a', 'youwu'), ('a', 'today') , ('b', '今日'), ('b', '有悟') )
select *
from seq ;

得到

title  name
-----  -----
a      youwu
a      today
b      今日
b      有悟

为了使用递归查询来拼接数据,需要先为每行数据编号(排序)。

with
    seq(title, name) as
    ( values ('a', 'youwu'), ('a', 'today') , ('b', '今日'), ('b', '有悟') )
    , seq_ordered(title, name, row_no) as
    ( select title, name, ROW_NUMBER() OVER( partition by title order by name)
      from seq
    )
select *
from seq_ordered

可以得到:

title  name   row_no
-----  -----  ------
a      today  1
a      youwu  2
b      今日    1
b      有悟    2

这样就可以使用递归循环了(有初始条件,row_no = 1)。试试看递归循环的逻辑会产生什么结果,

with recursive
    seq(title, name) as
    ( values ('a', 'youwu'), ('a', 'today') , ('b', '今日'), ('b', '有悟') )
    , seq_ordered(title, name, row_no) as
    ( select title, name, ROW_NUMBER() OVER( partition by title order by name)
      from seq
    )
    , lop(title, names, row_no) as
    ( select title, name, row_no
      from   seq_ordered
        where  row_no = 1
        union  all
        select lop.title, lop.names || '.' || seq.name, seq.row_no
        from   lop, seq_ordered seq
        where  lop.title = seq.title
        and    lop.row_no + 1 = seq.row_no
    )
select *
from lop ;

记得加上 recursive 关键字(虽然sqlite中不强制)。使用 seq_ordered 子查询 row_no = 1 做为起始行,按 “row_no 增加 1” 的方式循环从 seq_ordered 中找出数据,并把 name 连到一起。在 recurive-query 部分,使用了条件 lop.title = seq.title,因此字符串连接仅在相同的 title 值间进行,结果为:

title  names        row_no
-----  -----------  ------
a      today        1
b      今日          1
a      today.youwu  2
b      今日.有悟      2

似乎还久点什么 🧐。需要从这个结果中挑出目标行。从条件 lop.row_no + 1 = seq.row_no 看,循环按 row_no 逐次递增,意味着目标行的 row_notitle 分组内值最大,故而:

...
    , selected(title, names, select_no1) as
    ( select title, names, ROW_NUMBER() over(partition by title order by row_no desc)
      from   lop
    )
...

使用 row_number() over() 分析函数对上一步结果按 row_no 的值按降序排列。只要查询 selected 子查询,并筛选 select_no1 = 1 即可得到目标数据行。

本例在 sqlite3 环境中完成。

🧐 作业: 在 postgres、mysql、oracle 中使用相同的递归查询实现 ?看能不能一次性准确的写出来。


使用自定义函数 #

字符串拼接带有分组聚合的特征,如果要自定义函数,意味着函数:

  1. 必须可以分组聚合的SQL中使用;
  2. 函数接收多行数据(数据集,如游标、集合、数组等);
  3. 函数返回一行类型为字符串的数据。

推荐一篇文章, String Aggregation Techniques , 内容非常精彩,它演示了在 oracle 中使用 pl/sql 自定义函数来实现的多种方式,分别有数组、游标的情况。还用了 oracle 专用的 connect bysys_connect_by_pathmax() keep (dense_rank order...) 这种显摆的技巧,是学习 oracle pl/sql 编程的不错案例。


本文涉及到数据库分组聚合字符串连接函数,还有递归查询、分析函数、构建简易数据集等方面的知识。

关于递归查询、分析函数,可看本《thinking in sql》系列相关文章