整理SQL常用函数.docxVIP

一、STUFF 函数create table test (id int,?value?varchar(10))insert into testvalues(1,aa)insert into testvalues(1,bb)insert into testvalues(2,aaa)insert into testvalues(2,bbb)insert into testvalues(2,ccc)insert into testvalues(3,aa)insert into testvalues(4,bb)select * from testselect id, [values] = stuff((select , + [values] from test t where id = test.id for xml path()) , 1 , 1 , ) from test group by id stuff(param1, startIndex, length, param2)说明：将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。*/二、COUNT函数COUNT()函数用于返回一个列内所有非空值的个数，这是一个整型值。?由于COUNT(*)函数会忽略NULL值，所以这个查询的结果是2。三、SUM()函数SUM()函数是最常用的聚合函数之一，它的功能很容易理解：和AVG()函数一样，它用于数值数据类型，返回一个列范围内所有非空值的总和。四、CAST()函数 CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。以下例子用于将文本字符串123转换为整型: SELECT CAST(123 AS int) 返回值是整型值123。如果试图将一个代表小数的字符串转换为整型值，又会出现什么情况呢？ SELECT CAST(123.4 AS int) CAST()函数和CONVERT()函数都不能执行四舍五入或截断操作。由于123.4不能用int数据类型来表示，所以对这个函数调用将产生一个错误。Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value 123.4 to a column of data type int. 在将varchar 值123.4 转换成数据类型int 时失败。 要返回一个合法的数值，就必须使用能处理这个值的数据类型。对于这个例子，存在多个可用的数据类型。如果通过CAST()函数将这个值转换为decimal类型，需要首先定义decimal值的精度与小数位数。在本例中，精度与小数位数分别为9与2。精度是总的数字位数，包括小数点左边和右边位数的总和。而小数位数是小数点右边的位数。这表示本例能够支持的最大的整数值是9999999，而最小的小数是0.01。 SELECT CAST(123.4 AS decimal(9,2))decimal数据类型在结果网格中将显示有效小数位:123.40 精度和小数位数的默认值分别是18与0。如果在decimal类型中不提供这两个值，SQL Server将截断数字的小数部分，而不会产生错误。 SELECT CAST(123.4 AS decimal) 结果是一个整数值：123五、CONVERT()函数 对于简单类型转换，CONVERT()函数和CAST()函数的功能相同，只是语法不同。CAST()函数一般更容易使用，其功能也更简单。CONVERT()函数的优点是可以格式化日期和数值，它需要两个参数：第1个是目标数据类型，第2个是源数据。CONVERT()函数还具有一些改进的功能，它可以返回经过格式化的字符串值，且可以把日期值格式化成很多形式。有28种预定义的符合各种国际和特殊要求的日期与时间输出格式。六、STR()函数 这是一个将数字转换为字符串的快捷函数。这个函数有3个参数：数值、总长度和小数位数。如果数字的整数位数和小数位数(要加上小数点占用的一个字符)的总和小于总长度，对结果中左边的字符将用空格填充。在下面第1个例子中，包括小数点在内一共是5个字符。结果显示在网格中，显然左边的空格被填充了。这个调用指定，总长度为8个字符，小数位为4位： SELECT STR(123.4, 8, 4) 结果值的右边以0填充：123.4000。 下面给函数传递了一个10字符的值，并指定结果包含8个字符，有4个小数位： SELECT STR(123.456789, 8, 4)只有将这个结果截断才能符合要求。STR()函数对最后一位进行四舍五入：123.4568。现在，如