写sql会经常用到一些函数,但sqlserver和oracle的有些函数不同,我列举一些常用到得函数。没有列举数学函数,数学函数两者差不多。
函数对比
表格中颜色说明:
函数名相同、用法相同,一种颜色;函数名不同、函数名相同但用法不同,两种颜色;函数名不区分大小写。
ps:Oracle和sqlserver:字符串是以1为起始位置.
Oracle |
sqlserver |
说明 |
ASCII() |
ASCII() |
返回字符表达式最左端字符的ASCII 码值 注意:如果传入值为汉字,两个数据库返回的结果可能不一样 |
CHR() |
CHAR() |
将ASCII 码转换为字符 |
LOWER() |
LOWER() |
所有的字符变为小写 ,返回字符串 |
UPPER() |
UPPER() |
所有的字符变为大写,返回字符串 |
TO_CHAR() |
STR() |
把数值型数据转换为字符型数据。 |
LTRIM() |
LTRIM() |
把字符串头部的空格去掉 |
RTRIM() |
RTRIM |
把字符串尾部的空格去掉 |
SUBSTR (expression>, <start_pos>, length) |
SUBSTRING (expression>, <star_ pos>, length) |
返回从字符串左边第starting_ position 个字符起length个字符的部分。 |
INSTR ( <expression>, <’substring_ expression’>, ,int i, int k) |
CHARINDEX (<’substri_ expression’>, <expression>, <k>)
|
其中substring _expression 是所要查找的字符表达式,expression 可为字符串也可为列名表达式。 I 搜索的开始位置,默认为1 注:(1)oracle和sqlserver两个可接受参数的最大个数不一样,oracle对应的函数可接受参数的最大数为4,ersqlserver为3, (2)oracle和sqlserver对应的函数前两个参数的顺序不一样 |
无 |
QUOTENAME() |
返回被特定字符括起来的字符串。 |
无 |
SPACE< int_expression> () |
返回一个有指定长度的空白字符串。如果integer_expression 值为负值,则返回NULL 。 |
无 |
STUFF (<char_expre1>, <start_ position>, <length>,<char_expre2>) |
用另一子串替换字符串指定位置、长度的子串。 如果起始位置为负或长度值为负,或者起始位置大于character_expression1 的长度,则返回NULL 值。 |
initcap() |
无 |
返回字符串并将字符串的第一个字母变为大写,別的字母變為小寫; |
REPLACE( <string_expre1, <string_expre2>, <string_expre3> ) |
REPLACE (<string_expre1>, <string_expre2>, <string_expre3>) |
用string_expression3 替换在string_expression1 中的子串string_expression2。 |
GREATEST |
无 |
返回一组表达式中的最大值,即比较字符的编码大小. |
LEAST |
无 |
返回一组表达式中的最小值 |
SOUNDEX |
soundex 符组成的代 码 (SOUNDEX), 用于评估两个字符 串的相似性。 |
注:函数名称一样,用法不一样 Oracle该函数的用法:返回一个与给定的字符串读音相同的字符串 sqlserver该函数的用法:返回一个由四个字符组成的代码(SOUNDEX),用于评估两个字符串的相似性。 |
无 |
PATINDEX (<’%substr _expre%’>, <colu_ name> ) |
返回字符串中某个指定的子串出现的开始位置。 其中子串表达式前后必须有百分号“%”否则返回值为0。 与CHARINDEX 函数不同的是,PATINDEX函数的子串中可以使用通配符,且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。 |
LENGTH |
len |
返回参数长度 |
DUMP(s,fmt, start,length) |
无 |
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值 |
无 |
reverse函数 |
反转字符串 |
无 |
LEFT (<charr_expre>, <int_expre>) |
返回character_expression 左起 integer_expression 个字符。 |
无 |
RIGHT (<char_expre>, <int_expre>) |
返回character_expression 右起 integer_expression 个字符。 |
TO_DATE(string, format) |
CONVERT
|
注:这儿只说明转换为date类型时的用法 |
ADD_MONTHS |
无 |
增加或减去月份 。SqlServer中年月日的加减统一用DATEADD |
MONTHS_ BETWEEN |
无 |
给出date2-date1的月份。SqlServer用DATEDIFF |
LAST_DAY |
无 |
返回日期的最后一天 |
NEXT_DAY (date,day) |
无 |
给出日期date和星期x之后计算下一个星期的日期 |
无 |
Day (date_expre) |
返回date_expression中的日期值 |
无 |
Month (date_expre) |
返回date_expression中的月份值 |
无 |
Yea r(date_expre) |
返回date_expression中的年份值 |
无 |
DATEADD (<datepart>, <number>, <date>) |
返回指定日期date 加上指定的额外日期间隔number 产生的新日期。 |
无 |
DATEDIFF (<datepart>, <date1>, <date2>) |
返回两个指定日期在datepart 方面的不同之处,即date2 超过date1的差距值,其结果值是一个带有正负号的整数值。 |
无 |
DATENAME ( <datepart>, <date>) |
以字符串的形式返回日期的指定部分此部分。由datepart 来指定。 |
无 |
DATEPART (<datepart>, <date>) |
以整数值的形式返回日期的指定部分。此部分由datepart 来指定。 |
Oracle用sysdate 表示当前时间 |
GETDATE() |
以DATETIME 的缺省格式返回系统当前的日期和时间。 |
DECODE(col| expression, search1, r esult1 result2,...,] |
无 |
Decode()函数可以替代case语句,前面的用法相当于如下case语句: Case condition when search1 then result1 when search2 then result2 |
NVL (expr1, expr2) |
IsNULL (expr1, expr2) |
expr1为NULL,返回expr2;不为NULL,返回expr1。注意两者的类型要一致 |
NVL2 (expr1, expr2, expr3) |
无 |
expr1不为NULL,返回expr2;为NULL,返回expr3。expr2和expr3类型不同的话,expr3会转换为expr2的类型 |
NULLIF (expr1, expr2) |
无 |
相等返回NULL,不等返回expr1 |
COALESCE (expr1, expr2, ..., exprn) |
无 |
返回表达式列表中的第一个非空表达式 |
CONVERT (c,dset,sset) |
CONVERT (<data_ type> [ length ], <expre> [, style]) |
Oralce中的函数用法:将源字符串 sset从一个语言字符集转换到另一个目的dset字符集 sqlserver中的函数用法注意事项: 1)data_type为SQL Server系统定义的数据类型,用户自定义的数据类型不能在此使用。 |
EMPTY_BLOB()和EMPTY_CLOB() |
|
这两个函数都是用来对大数据类型字段进行初始化操作的函数 |
统计函数 AVG ( ) -返回的平均价值 -返回的行数 first_value() -返回的最大价值 -返回最小的价值 -返回的总和 |
统计函数 AVG ( ) -返回的平均价值 -返回的行数 -返回第一个值 -返回最后一个值 -返回的最大价值 -返回最小的价值 -返回的总和 |
统计函数:AVG、count()、max()、min()、sum()用法一样, Oracle不支持top函数。 |
row_number() over( partition by ... order by ... )
|
row_number() 例子: select row_number() Over (order by field1) as row_number, * from t_table |
注:在oralce中:(partition by)/order by两者出现其一或者同时出现,不能都不出现,partition相当于group的功能 开窗函数概念:开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化, Sqlserver中,使用时加over (order by 栏位),以那个栏位排序,rownumber建立在这个排序后的列上 |
rank() over (partition by ... order by ...) |
Rank() Over (order by field1) |
不连续排名,用法和row_numbe类似 |
dense_rank() over( partition by ... order by ... ) |
dense_rank()over (order by field1) |
连续排名,用法和row_numbe类似 |
列转行练习题
1. 有表如下
TeacherID:代表教师ID;day:代表星期 ;isHAS:代表是否有课。
TeacherID |
day |
isHAS |
1 |
2 |
有 |
1 |
3 |
有 |
2 |
1 |
有 |
3 |
2 |
有 |
1 |
2 |
有 |
1 |
2 |
無 |
把上面数据转换为如下格式
TeacherID 星期一 星期二 星期三
1 NULL 2 1
2 1 NULL NULL
3 NULL 1 NULL
2.现有一个商品销售表sale,表结构为:
month char(6) --月份
sell number(10,2) --月销售金额
现有数据为:
200001 |
1000 |
200002 |
1100 |
200003 |
1200 |
200004 |
1300 |
200005 |
1400 |
200006 |
1500 |
200007 |
1600 |
200008 |
1700 |
200009 |
1800 |
200010 |
1900 |
200011 |
2000 |
200012 |
2100 |
200212 |
4400 |
200112 |
3300 |
200101 |
9999 |
200102 |
3333 |
200103 |
2331 |
想要转化为以下结构的数据:
年份 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月
2000 1000 1100 1200 1400 1500 1600 1700 1800 1900 2000 2100 2200
2001
2002
注:图片没法直接复制,我后补的上面的转化结果,数据不全,表达的意思是把对应数据填充。
这两道题用decode很简单实现的,也可以用case语句,答案后续给出。
总结
sql函数很多,经常用到一些不是特别多,平时多练习,sql函数就慢慢的熟悉了。勤动手,多动脑,一切so easy!
菜鸟之作,有错误请指出,我后续会修改。谢谢!