SQL Server进阶（十二）常用函数

序言

SELECT datediff(ms, '2019-07-18 14:01:50.867', '2019-07-18 14:01:52.877')

coalesce函数（下面简称函数），返回一个参数中非空的值

SELECT  COALESCE(NULL, NULL, GETDATE())

由于两个参数都为null，所以返回getdate（）函数的值，也就是当前时间。即返回第一个非空的值。由于这个函数是返回第一个非空的值，所以参数里面必须最少有一个非空的值，如果使用下面的查询，将会报错：

SELECT  COALESCE(NULL, NULL, NULL)

生成随机数

select cast(ceiling(rand(checksum(newid()))*11)as int ) --生成1至11之间任一整数
cast( floor(rand()*100) as int)  --生成0至99之间任一整数 0至N-1之间
cast(ceiling(rand() * 100) as int) --生成1至100之间任一整数 1至N之间

也可以使用top 1

SET ROWCOUNT 1

SET ROWCOUNT 0

FOR XML PATH

利用 T-sql 的从句 for xml path('') 实现多行合并到一行, 并带有分隔符

SELECT ',' + au.UserCode FROM dbo.AcceptanceUser au  FOR XML PATH ('')
SELECT  STUFF((SELECT ',' + au.UserCode FROM dbo.AcceptanceUser au  FOR XML PATH ('')), 1, 1, '')

https://blog.csdn.net/rav009/article/details/50723307

DATE

select GETDATE()
select STR(YEAR(GETDATE()))
select RTRIM(STR(YEAR(GETDATE())))
select LTRIM(RTRIM(STR(YEAR(GETDATE()))))

DECLARE @now DATETIME 
SELECT  @now = GETDATE() 
SELECT    DATEPART(YEAR, @now)
SELECT    DATEPART(MONTH, @now)
SELECT    DATEPART(DAY, @now)

CONVERT

　　data_type(length) 规定目标数据类型（带有可选的长度）。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。

CONVERT(data_type(length),data_to_be_converted,style)SELECT    
CONVERT(VARCHAR(19),GETDATE())
CONVERT(NVARCHAR(12),GETDATE() ,112)
CONVERT(VARCHAR(10),GETDATE(),110) 
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

select @@rowcount;

DATEDIFF() 函数返回两个日期之间的时间。

DATEDIFF(datepart,startdate,enddate)

SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate

DATEADD() 函数在日期中添加或减去指定的时间间隔。

select DATEADD(mm,2,'2014-12-30')    结果：2015-02-28

ISNULL

STUFF

RTRIM

REPLACE

FOR XML PATH('')

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

  SELECT  ISNULL(STUFF(( SELECT   DISTINCT  ',' + RTRIM(AreaName) FROM  [Sign2019].[dbo].[AreaInfo] WHERE Level=1 FOR XML PATH('')), 1, 1, ''), '')

以下示例从第一个字符串 abcdef 的第 2 个位置 (b) 开始删除三个字符，然后在删除位置插入第二个字符串，从而创建并返回一个字符串。

aijklmnef

SUBSTRING ( expression, start, length )

dense_rank()

SELECT   dense_rank() OVER (ORDER BY score DESC) AS Rank,score FROM [SCST].[dbo].[Sc]

WITH (NOLOCK)

除了简单的SELECT之外，有JOIN的SELECT语法也是可以使用的。但是DELETE、INSERT、UPDATE这些需要transaction的指令就不行了

https://www.cnblogs.com/kerrycode/p/3946268.html

SCOPE_IDENTITY()和@@IDENTITY

　　返回上面操作最后一个数据表的最后row的IDENTITY 列的值；

　　建议大家慎用 @@IDENTITY，而尽量采用 SCOPE_IDENTITY() 函数替换之。SCOPE_IDENTITY() 也是得到最后一条自增域的值，但是它是仅限在一个操作范围之内，而不像 @@IDENTITY 是取全局操作的最后一步操作所产生的自增域的值的。

isnull()函数：

isnull(value1,value2)

1、value1与value2的数据类型必须一致。

2、如果value1的值不为null，结果返回value1。

3、如果value1为null，结果返回vaule2的值。vaule2是你设定的值。

Replace

RowNumber

Convert

DISTINCT

if...eles

IF(条件表达式)
  BEGIN --相当于C#里的{
    语句1
  ……
  END --相当于C#里的}
ELSE
 BEGIN
    语句1
    ……
  END

declare

DECLARE @sql VARCHAR(8000)
DECLARE @start_row_num AS INT

case

select SName,Sage=(
    case Sage
        when 17 then '小'
        when 18 then '正好'
        when 19 then '大'
    end
)    
from [SCST].[dbo].[Student]

select和SET的区别

CAST 和 CONVERT

SELECT CAST(SYSDATETIME() AS DATE);
SELECT CONVERT(CHAR(8),CURRENT_TIMESTAMP,112);
SELECT CAST(CONVERT(VARCHAR(12), getdate(), 111) AS DATE)

需要注意的是，CAST是ANSI标准的SQL，而CONVERT不是。所以，除非需要使用样式值，否则推荐优先使用CAST函数，以保证代码尽可能与标准兼容。

CROSS APPLY

SQL中PIVOT 行列转换

　　https://www.cnblogs.com/zouhao/p/6050809.html

透视转换技术

　　https://www.cnblogs.com/yuanzijian-ruiec/p/9457366.html

SET NOCOUNT的作用

SELECT DB_NAME()

四舍五入

select ROUND(32.678,2) --32.700
select CAST(32.678 as DECIMAL(18,2)) --32.7
select convert(NUMERIC(18,2),32.678) --32.7

资料

https://www.cnblogs.com/lqLove/p/5803717.html

https://www.cnblogs.com/rdst/p/4727063.html

https://blog.csdn.net/smartsmile2012/article/details/73286087