写了一个稍微复杂点的sql心得

最新推荐文章于 2023-04-26 09:26:02 发布

zwqq01

最新推荐文章于 2023-04-26 09:26:02 发布

阅读量241

点赞数

分类专栏： oracle 文章标签： sql oracle c

本文链接：https://blog.csdn.net/zwqq01/article/details/5537809

版权

oracle 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

select pcode from (
        select h.k,h.d,h.m,h.u,l.areacode,
               l.areacode||substr(h.u,4,length(h.u))||h.k pcode
         from(
        select j.k,j.d,j.m,j.u from(
        select lpad(f,3,0) as k,d, m,u from(
        select xx.areacode d,nvl(count(cy.pcode),'001')f,mx.m,mx.shopcode u
        from cz_s_jtxx xx, cz_s_jtcy cy ,
          ( select a.m,d.shopid,d.shopcode from
        (select shopid m,parentid from s_shop where shoptype=1) a,
        (select shopid,parentid from s_shop where shoptype=2) b,
        (select shopid,parentid from s_shop where shoptype=3)c,
        (select shopid,parentid,shopcode from s_shop where shoptype=4)d
        where d.parentid=c.shopid
        and c.parentid=b.shopid
        and b.parentid=a.m
        and d.shopid=14725) mx
        where xx.pcode = cy.pcode
        and xx.areacode=mx.shopid
        group by xx.areacode,mx.m,mx.shopcode))j)h,
        (select areacode,shopid from cz_s_area) l
       where h.m=l.shopid)
首先从最里层的 sql说起：

select a.m,d.shopid,d.shopcode from
        (select shopid m,parentid from s_shop where shoptype=1) a,
        (select shopid,parentid from s_shop where shoptype=2) b,
        (select shopid,parentid from s_shop where shoptype=3)c,
        (select shopid,parentid,shopcode from s_shop where shoptype=4)d
        where d.parentid=c.shopid
        and c.parentid=b.shopid
        and b.parentid=a.m
        and d.shopid=14725

这条sql能让我们根据机构找到最上级的shopid，和shopcode 当然这种写法不是唯一的我们可以用oracle的

start with ...connect by prior 函数来处理这个

不过我这里没采用，当有了这个以后我么能查出一条唯一的条目，根据这个条目我们可以把他看作一个表作为子表来处理

       select lpad(f,3,0) as k,d, m,u from(
        select xx.areacode d,nvl(count(cy.pcode),'001')f,mx.m,mx.shopcode u
        from cz_s_jtxx xx, cz_s_jtcy cy ,
          ( select a.m,d.shopid,d.shopcode from
        (select shopid m,parentid from s_shop where shoptype=1) a,
        (select shopid,parentid from s_shop where shoptype=2) b,
        (select shopid,parentid from s_shop where shoptype=3)c,
        (select shopid,parentid,shopcode from s_shop where shoptype=4)d
        where d.parentid=c.shopid
        and c.parentid=b.shopid
        and b.parentid=a.m
        and d.shopid=14725) mx
        where xx.pcode = cy.pcode
        and xx.areacode=mx.shopid
        group by xx.areacode,mx.m,mx.shopcode)

通过这个等值链接我们可以确定上一个步骤所对应出的家庭xx表的对应条目这时