oracle 和 sqlserver sql语句的差别

select创建表

oracle: create table table2 as (select * from table1);

sqlserver: select * into table2 from table1;

递归查询

oracle: connect by

select ... from <TableName>

where <Conditional-1>

start with <Conditional-2>

connect by <Conditional-3>

sqlserver: cte

;with cte(id, name, pid, lvl) as

(

select t.*, 0 lvl from t where pid=0

union all

select t.*, cte.lvl+1 lvl from t, cte where t.pid = cte.id

)

select

case

when lvl=0 then name

else REPLICATE('　', lvl) + '└' + name

end,

id,

pid

from cte

;

正则

oracel: regexp_substr, regexp_like, regexp_instr, regexp_replace, regexp_count

sqlserver: 木有

子查询

oracle: select * from (select * from table1);

sqlserver: select * from (select * from table1) t;

组内排序（一致）

oracle: row_number() over (partition by ... order by ...)

sqlserver: row_number() over (partition by .. order by ...)

查询结果插入（一致）

insert into table1(col1, col2, col3) select col1, col2, col3 from table2;