摘要
本文主要针对Oracle和Mysql下将多列或者多行数据拼接的语法解释
本文主要针对Oracle和Mysql下将多列或者多行数据拼接的语法解释
按照某列进行分组后,查询出的多行其列拼接,例如有如下表
name | subject_1 |
zhangsan | 数学 |
zhangsan | 语文 |
lisi | 英语 |
想按照name分组,将该name所有的subject_1拼成一列,拼接成如下的效果:
name | subject_1 |
zhangsan | 数学;语文 |
lisi | 英语 |
SELECT NAME, wmsys.wm_concat(subject_1) subject_1 FROM table_name GROUP BY NAME
注意:
1、此为Oracle 10提供的语法,在10g版本中,返回字符串类型,在11g版本中返回clob类型。使用时直接用wm_concat也可以。
2、上述语法默认只能以“,”分隔,如想自定义分隔符,需要重写wm_concat函数。
该语法结构如下:
LISTAGG(measure_expr [,'delimiter'])WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
其参数说明如下:
参数 | 说明 |
OVER | 使用 OVER 子句定义窗口进行计算。 |
measure_expr | 可以是任何表达式。度量列中的空值将被忽略。 |
delimiter | 指定用于分隔度量值的字符串。此子句是可选的,默认为 NULL。 |
查询sql如下:
SELECT NAME, listagg(subject_1 , ',') within GROUP (ORDER BY NAME) FROM table_name GROUP BY NAME
注意:
1、该函数在Oracle 11g 版本中推出,对分组后的数据按照一定的排序进行字符串连接
2、“[,]”表示字符串连接的分隔符
该语法结构如下:
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
利用上面的语法即可通过以下SQL实现
SELECT NAME, GROUP_CONCAT(distinct subject_1 order by subject_1 asc separator ',') subject_1 FROM table_name GROUP BY NAME
上面的语法中分别运用了distinct(去冗余)、order by (排序)、separator(分隔符),您可以根据自己所需使用条件。
例如表tk_user_info:
name | age | address |
张三 | 12 | 北京市朝阳区 |
李四 | 23 | 天津市南开区 |
想要达到如下查询效果
name | msg |
张三 | 12|北京市朝阳区 |
李四 | 23|天津市南开区 |
oracle中通过wm_concat(),即可以实现对多列的拼接:
select NAME, wmsys.wm_concat(age || '|' || address) msg from TK_USER_INFO group by NAME;
注意:
1、上述SQL中|| '|' || 表示两个列以|分隔,'|'可以换为任意分隔符
select name, CONCAT(age ,address) msg from tk_user_info;
注意:
1、CONCAT拼接的结果不带分隔符
2、如果右任意拼接列为null,则返回null
select name, CONCAT_WS(',',age ,address) msg from tk_user_info;
注意:
1、此函数可以定义分隔符,CONCAT_WS() 代表 CONCAT With Separator
2、其语法为CONCAT_WS(separator,str1,str2,…),第一个参数为分隔符,其他为拼接列
select name, GROUP_CONCAT(age,',',address) msg from tk_user_info group by name;
注意:
1、其语法为(像上面多行拼接的解释中一样,可以进行去冗余、排序、分隔符-可以不同的拼接列后面有不同的分隔符):
GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]] [SEPARATOR str_val])
2、此sql用于对某列分组后的各列拼接,因此SQL后必须带有group by