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;