sql语句

最新推荐文章于 2019-08-07 22:27:07 发布

原创最新推荐文章于 2019-08-07 22:27:07 发布 · 127 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

sql语句专栏收录该内容

7 篇文章

订阅专栏

本文介绍了Oracle数据库中使用connect by、prior和start with进行层级查询的方法，并展示了一个排除特定条件用户的查询实例。

1.oracle中的语句connect by 、prior、start with的用法

select *
  from (select *
          from (select *
                  from cms_category
                 where site_id = 'www'
                   and (ename = 'root' or
                       (ename <> 'root' and SUBJECTTYPE = '0'))) n
        connect by prior id = father_id
         start with father_id = '-1'
         order siblings by sort_id)

2.查询不具有keyid为52的用户

 select uk.keyid ,uk.userid ,u.*, o.full_Cname as orgFullCname
   from pbo_user u left join pbo_org o on (u.orgid = o.id) left join
  (select * from pbo_r_user_key where keyid = '52') uk
   on(u.userid = uk.userid) where
  1 = 1
                                      and (uk.keyid is null or
                                          uk.keyid <> '52')