SQL Sever 2008和Oracle 10g sql语句的区别：递归、拼音排序

1、递归查询

　　Orcale 10g：

　　　　select level, m.pid,m.nodename,m.nodemark nodemark,m.parentid,m.nodeurl,m.classname
　　　　　　from (select distinct m.pid pid,m.nodename nodename,m.nodemark nodemark,m.parentid parentid,m.nodeurl nodeurl,
　　　　　　　　　　m.classname classname,ordernum from bs_t_sm_menutree m, bs_t_sm_rolemenutree rm where m.pid = rm.menuid
　　　　　　　　　　and m.status = 1 and rm.roleid in = ?) m start with parentid = '0' connect by parentid = prior pid order siblings by m.ordernum

　　SQL Server 2008： with myTb as(select pid,nodename,nodemark,parentid,nodeurl,classname,ordernum, cast(right(100000 + ordernum,5) as 　　　　　　　　　　　varchar(1000)) as dns, 1 as level  from BS_V_SM_MENUBYROLE m where m.roleid in =? and m.parentid = '0'  union all  select 　　　　　　　　　　　　　　　b.pid,b.nodename,b.nodemark,b.parentid,b.nodeurl,b.classname,b.ordernum, cast(myTb.dns+'-'+cast(right(100000+b.ordernum,5) as varchar(5)) 　　　　　　as varchar(1000)) as dns, level+1 as level from myTb inner join BS_V_SM_MENUBYROLE b on myTb.pid=b.parentid and b.roleid in = ？) select 　　　　　　　distinct * from myTb t order by t.dns

2、排序：

　　1、按照汉字的拼音排序：

　　Oracle 10g: select * from bs_t_sm_user t order by nlssort(t.fullname, 'NLS_SORT=SCHINESE_PINYIN_M');

　　SQL Server 2008: select * from bs_t_sm_user t order by t.fullname COLLATE Chinese_PRC_CS_AS_KS_WS