SQL 高级语法

复制表数据

复制表结构以及数据：
    create table new as select * from old
复制数据到一个相同的表：
    insert into sametable select * from old
只复制表结构：
    create table new as select * from old where 1 = 2   

树形结构表查询

查询某个节点下面的所有子节点

select * from tb_deptconfig start with parent_id = -1 connect by prior id = parent_id

查询某个节点上面的所有父节点

select * from tb_dept start with id = '44060' connect by prior parent_id = id;

查询某个节点的根节点

select name,connect_by_root(name),connect_by_root(id) from tb_deptconfig   start with id = '440600000094' connect by prior parent_id = id;

查询某个节点的所有叶子节点

select * from tb_deptconfig where connect_by_isleaf = 1 start with parent_id = '-1' connect by  parent_id =prior id;

查询某个节点的父亲节点路径

select name,sys_connect_by_path(name,'--') from tb_deptconfig start with id = '44060' connect by prior parent_id = id;

查询某个节点的子节点路径

select name,sys_connect_by_path(name,'--') from tb_deptconfig start with  parent_id = -1 connect by prior id = parent_id   

case when表达式

(case when b.oritation = '0'
    then 100
    case when b.oritation = '1'
    then 200
else 300) as id 

ege:

select 
    b.[_id] ,
    b.ORIENTATION, 
    c.[USER_ID],      
from record b 
left join     
        (select USER_ID,NAME from contact     
    union 
        select USER_ID,NAME from FRIENDCONTACT) c  
on 
    (case when b.ORIENTATION = '0' then 10     
    else b.[USER_ID] end ) = c.[USER_ID]   
    where  b.[USER_ID]= "+mUserId +
     "  order by b.RECEIVE_TIME desc "

更新设置数据

有AB表，A表数据完整，将A表的某些字段更新到B中

update B set B(B1,B2,B3) = (select A1,B2,B3 from A,B where A.id = B.id)