Sql语言如何拼接数据?

   在sql语言中对数据进行适当的操作,能够有效地减少查询出数据之后业务逻辑的代码量,因此本篇博文将介绍sql语言中的拼接数据的功能。

   本文将介绍拼接字段、拼接列,以及在一定的限制下拼接同一字段中不同列数据的功能。Mysql和Sql Server在这项功能中各有不同,因此分开介绍。

   以下图中的表格为例:

Sql Server

   1.符号 +

select Score1 + Score2 from Mytable where ID = 1;

   由于Score1和Score2的字段都是整型,所以返回结果为两字段之和,即返回157;如果选择用于拼接的数据不是整型,则sql会对数据进行拼接,例如:

select Name + Score1 From MyTable where Id = 1;

   由于被选中的字段不是整型,返回结果应当为两字段的拼接结果,即返回A90;

   2.将一整列的值拼接为一个字符串 for xml path

   在Sql Server中可以将数据以xml格式显示,for xml path就是显示为xml的一种方式。

select ',' + Name from MyTable for xml path('');

   如上语句,返回的结果为

,A,B,C,D,A,D,E

   此处需要注意,语句末尾的('')不可少,他的功能是为了去除表格中的行标志,因为在数据库表中,这些数据来自同一列的不通行,需要使用这种方法去除行标志,不然数据在xml中仍然会以列的形式呈现。

   在此稍作补充:由返回的结果我们可以看到,第一个A前面多了一个逗号,这是在查询语句中就标明了的,如果想要去除这个逗号,就需要用到stuff函数。

   STUFF(string,insert position,delete count,string inserted)函数的功能是将一个字符串插入到另一个字符串中。插入时,插入的字符串可以覆盖原有的指定数量的字符。

 第一个参数string,指的就是原字符串,即需要被改变的字符串,它可以是一个固定字符串,也可以指定为数据库中的某一字段列,在这里我们填写的是经过for xml path处理过的字段

 第二个参数insert position,指插入字符串的位置,Sql Server中默认是从1开始,而非从0开始,在这里我们需要删除字符串首的逗号,因此我们的insert position为1

 第三个参数delete count,指的是要删除的字符个数,从insert position删除指定的个数,如果count为0表示不删除,我们只需要删除一个逗号,因此delete count也为1

 第四个参数string inserted,表示要插入的字符串,在删除过后,可以在删除的位置插入想要的字符串,如果不插入字符串,则第四个参数即为两个单引号

   综上所述,我们的语句应为:

select stuff((select ',' + Name from MyTable for xml path('')),1,1,'');

   这样返回的结果就是:

A,B,C,D,A,D,E

MySQL

   1.符号 +

   在MySQL中也支持使用加号拼接结果,但是它的运行结果与MySQL中的运行结果有所不同。在两个字段都是整型时,MySQL与Sql Server运行结果相同,都会返回两个整型值得和,但是在两个字段中有一个为字符串时,MySQL的返回结果不是拼接两字符串,而是默认字符串为零,再返回两个字段之和。

//返回90
select Name + Score1 where ID = 1 ;

   如上所示,因为Name字段不是整型,所以相当于Score1 + 0,所以返回值就是Score1的值,即90;另外,如果相加的两个字段全都是字符串,则返回0。

   2.CONCAT

   CONTACT的功能是直接将数据按照字符串格式拼接,类似于Sql Server中加号拼接字符串的功能。

//与Sql Server中的"+"类似
select CONCAT(ID,Name,Score1) from MyTable where ID = 1;
//返回值为 1A90;

    在这里需要注意的是,CONCAT后面括号中的参数只要有一个值为null,整个函数的返回值就会为null。

   3.CONCAT_WS

   CONOCAT_WS的用法和前者类似,但是它的第一个参数为分隔符,在返回的值中,每一个参数之间都会有第一个参数作为分隔。

select CONCAT_WS('-',ID,Name,Score1) from MyTable where ID = 1;
//返回值为 1-A-90;

   如果CONCAT_WS的第一个参数为null,则返回值为null,如果后面的参数中有null,则这些参数会被忽略,只返回其他参数和分隔符组成的字符串。

   4.根据字段拼接 GROUP_CONTACT

 

   在我们使用数据库时,会碰到这样一种情况:同一个Name的人有两条数据,但是他们的其他数据并不相同,而我们所需要的是同一个Name的人的Score1集合,即需要获得Name为A的所有的Score1并且希望将其拼接为一个字段,这里我们就需要用到GROUP_CONCAT。

   函数语法如下:group_concat( 要连接的字段 )  [Order BY 排序字段 ASC/DESC]   [Separator '分隔符'] 

select Name,GROUP_CONCAT(Score1 Separator '-') FROM MyTable GROUP BY Name;

   其中GROUP BY 后面的字段是Name,所有同一排序字段的数据会被拼接后存入同一字段中,并以相应的分隔符分分隔。

如上的sql语句,返回值为:

   因为上面的数据中只有Name为A和D的两项数据有重复,所有他们的Score1是拼接出来的。

   值得注意的是GROUP_CONCAT是有长度限制的,MySQL对其的默认长度限制为1024,可以使用如下语句来进行修改长度:

SHOW VARIABLES LIKE "%group_concat_max_len%"//查看长度

SET SESSION group_concat_max_len = 102400; //修改长度

 

发布了19 篇原创文章 · 获赞 15 · 访问量 9万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览