Oracle 和 Mysql 多列/行拼接语法

作者:青山常在人不老   阅读 (546)  |  收藏 (0)  |  点赞 (0)

摘要

本文主要针对Oracle和Mysql下将多列或者多行数据拼接的语法解释


原文链接:Oracle 和 Mysql 多列/行拼接语法

1、多行拼接

按照某列进行分组后,查询出的多行其列拼接,例如有如下表


namesubject_1
zhangsan数学
zhangsan语文
lisi英语

想按照name分组,将该name所有的subject_1拼成一列,拼接成如下的效果:


namesubject_1
zhangsan数学;语文
lisi英语

1.1 oracle语法wmsys.wm_concat():

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函数。

1.2 oracle语法LISTAGG()(注意,此为Oracle 11g及以上版本提供的语法):

该语法结构如下:

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、“[,]”表示字符串连接的分隔符

1.3 mysql语法group_concat:

该语法结构如下:

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(分隔符),您可以根据自己所需使用条件。

2、多列拼接

例如表tk_user_info:

nameageaddress
张三12北京市朝阳区
李四23天津市南开区

想要达到如下查询效果

namemsg
张三12|北京市朝阳区
李四23|天津市南开区

2.1 oracle语法wmsys.wm_concat():

oracle中通过wm_concat(),即可以实现对多列的拼接:

select NAME, wmsys.wm_concat(age || '|' || address) msg
from TK_USER_INFO
group by NAME;

注意:

1、上述SQL中|| '|'  || 表示两个列以|分隔,'|'可以换为任意分隔符

2.2 mysql 语法CONCAT()

select name, CONCAT(age ,address) msg from tk_user_info;

注意:

1、CONCAT拼接的结果不带分隔符

2、如果右任意拼接列为null,则返回null

2.3 mysql 语法CONCAT_WS()

select name, CONCAT_WS(',',age ,address) msg from tk_user_info;

注意:

1、此函数可以定义分隔符,CONCAT_WS() 代表 CONCAT With Separator

2、其语法为CONCAT_WS(separator,str1,str2,…),第一个参数为分隔符,其他为拼接列

2.4 mysql 语法GROUP_CONCAT()

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 

分类   数据库设计
字数   2069

博客标签    WMSYS.WM_CONCAT   oracle行转列   Mysql行转列   group_concat语法  

评论