(转)Oracle/Mysql/SqlServer函数区别
1.类型转换 ????
--Oracle??select?to_number('123')?from?dual;??--123;??
?select?to_char(33)?from?dual;???????--33;??
select?to_date('2004-11-27','yyyy/mm/dd')?from?dual;--2004-11-27????
--Mysql??select?cast('123'?as?signed?integer);???--123???
select?cast(33?as?char(2));?????--33；??
select?to_days('2000-01-01');???????--730485????
--SqlServer???select?cast('123'?as?decimal(30,2));????
--123.00??select?cast(33?as?char(2));?????--33；??
select?convert(varchar(12)?,?getdate(),?120) ???
?2.四舍五入函数区别? ????
--Oracle??select?round(12.86*10)/10?from?dual;????--12.9????
--Mysql??select?format(12.89,1);?????????--12.9????
?--SqlServer??select?round(12.89,1);??????????--12.9?????
3.日期时间函数 ????
--Oracle??select?sysdate?from?dual;???????--日期时间?????
--Mysql??select?sysdate();???????????--日期时间???
select?current_date();??????????--日期????
--SqlServer??select?getdate();???????????--日期时间??
select?datediff(day,'2010-01-01',cast(getdate()?as?varchar(10)));--日期相差天数???
?
4.Decode函数? ????
--Oracle???select?decode(sign(12),1,1,0,0,-1)?from?dual;--1?????
--Mysql/SqlServer???select?case?when?sign(12)=1?then?1?when?sign(12)=0?then?0?else?-1?end;--1????
?5.判空函数? ????
--Oracle??select?nvl(1,0)?from?dual;??????--1?????
--Mysql??select?ifnull(1,0);?????????--1?????
--SqlServer??select?isnull(1,0);?????????--1?????
6.字符串连接函数 ????
--Oracle??select?'1'||'2'?from?dual;??????--12??
select?concat('1','2');?????????--12????
--Mysql??select?concat('1','2');?????????--12????
--SqlServer??select?'1'+'2';?????????????--12????
7.记录限制函数 ????
--Oracle??select?1?from?dual?where?rownum?<=?10; ????
--Mysql??select?1?from?dual?limit?10; ????
--SqlServer??select?top?10?1 ???
?8.字符串截取函数 ????
--Oracle??select?substr('12345',1,3)?from?dual; ????
--Mysql/SqlServer??select?substring('12345',1,3); ???
?8.把多行转换成一合并列 ????
--Oracle??select?wm_concat(列名)?from?dual;?--多行记录转换成一列之间用,分割????
--Mysql/SqlServer??select?group_concat(列名);??
9、中文排序
--Oracle??select?*?from?dual?order by? NLSSORT('CD.F_NAME_CH',NLS_SORT=SCHINESE_PINYIN_M) desc;?--中文拼音排序???
1）按笔画排序?
select * from Table order by nlssort(columnName,'NLS_SORT=SCHINESE_STROKE_M')?
2）按部首排序?
select * from Table order by nlssort(columnName,'NLS_SORT=SCHINESE_RADICAL_M')?
3）按拼音排序?
select * from Table order by nlssort(columnName,'NLS_SORT=SCHINESE_PINYIN_M');??
--Mysql????select?*?from?dual NLSSORT order by?CONVERT(‘F_NAME_CH' USING GBK) desc ;
??2）按拼音排序
Select * From [Table_Name] ORDER BY [Column_Name] COLLATE Chinese_PRC_CS_AS_KS_WS
如果数据表tbl的某字段name的字符编码是latin1_swedish_ci;
select * from `tbl` order by birary(name) asc?
如果数据表tbl的某字段name的字符编码是utf8_general_ci;
SELECT name FROM `tbl` WHERE 1
ORDER BY CONVERT( name USING gbk ) COLLATE gbk_chinese_ci ASC
--SqlServer?
1）中文的笔画顺序排序
Select * From [Table_Name] Order By [Column_Name] Collate Chinese_PRC_Stroke_ci_as
