SQL函数总结.xls

功能类型函数用法空值处理Oraclenvl(check_expression,replacement_value)第一个参数指要判断的字段,第二参数是指替换的值,如nvl(Categorization_Tier_1,'未填写')Categorization_Tier_1sqlserverisNull(check_expression,replacement_value)用法同上:如isnull(Count(Incident_Number),0)分析处理通用over(partitionby...orderby...)(tent/13/0304/16/)分组累加求和:sum()over(partitionby...orderby...),例如SUM(NUM)over(partitionbyCategory,Type,ItemorderbyMonth)TOTAL各分类按月累加求和SUM(NUM)over(orderbyMonth)TOTAL按月连续累加求和求第一个值及最后一个值first_value()over(partitionby...orderby...)求第一个值last_value()over(partitionby...orderby...)求最后一个值如:LAST_VALUE(CATEGORY)OVER(ORDERBYINCIDENTID)CATEGORY排名:rank()over(partitionby...orderby...)如:RANK()OVER(ORDERBYSUM(INCIDENTID)DESC)模糊匹配通用like用法:SELECTcolumn_name(s)FROMtable_nameWHEREcolumn_nameLIKEpattern如selectdistinctRequest_id02fromHPD_Associationswhereform_name01like'%HPD%'详细:/sql/()向下取整,如selectfloor()fromdual;返回5ceil()向上取整,如selectceil()fromdual;返回6round()四舍五入,如SELECTround(,2)FROMdual;()不四舍五入,如SELECTround(,0)FROMdual;返回5to_char()如selectto_char(,'')fromdual;小数后第三第四为不足补0,(data_type,expression[style])一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)相互转换的时候才用到。SelectCONVERT(varchar(100),GETDATE(),105):16-05-2006SelectCONVERT(varchar(100),GETDATE(),108):10:57:49SelectCONVERT(varchar(100),GETDATE(),110):0