sql server笔记

CONVERT(varchar(10),createtime,120)                                 --将日期转换成varhcar类型，120 指格式：yyyy-mm-dd
left(replace(convert(varchar(6),a.OrderDate,112),'-',''),6)         --提取日期字段中YYYYMM
len(CtripCardNO) = 0                                                               --判断字段值为空格
len(D1111111) = 8                                                                  --获取字符个数，不是字符长度
left(xxxxx,6),right(xxxxx,6)                                                      --截取字符串长度
datepart(yyyy,OrderDate)                                                       --相当于to_char(OrderDate,'yyyy')
isnull(g.Group_name, '')                                                          --使用指定的替换值替换 NULL
EXECUTE (@show)                                                                 --动态执行sql
SELECT [d],[x] into #test_Ls FROM [Temp_IT_MBDB].[dbo].[drhtest];   --使用临时表
datediff(day,day1,day2)                                             --返回day2-day1间隔天数

-----------------------------------------------------               --DATEPART
SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1
-----------------------------------------------------               --OVER(PARTITION BY XXX ORDER by XXX)
row_number()over(partition by class order by s desc)         --按照class分区，按照s排序,s值相同的话按先后顺序排,有重复的情况下不适合用来排名。
rank()over(partition by class order by s desc)           --按照class分区，按照s排序,s值相同的情况序号相同,同时会跳过一个序号。
dense_rank()over(partition by class order by s desc)         --按照class分区，按照s排序,符合通常排名情况。
sum(s)over(partition by class order by s desc)           --按照class分区，按照s累加汇总。