oracle 多表连接全关联,oracle 多表全连接

一张表里根据某个字段，获取各个字段的值，并进行表合并。

首先建立 零时表，在 如果是多表，先union all 排重，在用left join .

主要函数： 分组排序序号

row_number() over(partitionby NBBH00order by XSXH00)

with  xq10 as --星期一上午  10 ： 第一位表示 星期几 第二位 0表示上午 1表示下午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期一' and SXWBZ0='0' and yxbz00='1'),

xq11 as --星期一下午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期一' and SXWBZ0='1' and yxbz00='1'),

xq20 as --星期二上午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期二' and SXWBZ0='0' and yxbz00='1'),

xq21 as  --星期二下午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期二' and SXWBZ0='1' and yxbz00='1'),

xq30 as --星期三上午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期三' and SXWBZ0='0' and yxbz00='1'),

xq31 as --星期三下午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期三' and SXWBZ0='1' and yxbz00='1'),

xq40 as --星期四上午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期四' and SXWBZ0='0' and yxbz00='1'),

xq41 as --星期四下午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期四' and SXWBZ0='1' and yxbz00='1'),

xq50 as --星期五上午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期五' and SXWBZ0='0' and yxbz00='1'),

xq51 as --星期五下午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期五' and SXWBZ0='1' and yxbz00='1'),

xq60 as --星期六上午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期六' and SXWBZ0='0' and yxbz00='1'),

xq61 as --星期六下午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期六' and SXWBZ0='1' and yxbz00='1'),

xq70 as --星期天上午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期七' and SXWBZ0='0' and yxbz00='1'),

xq71 as --星期天下午

(select NBBH00 ,MC0000 ,YGXM00,row_number() over(partition by NBBH00 order by XSXH00)  rownumber

from VW_PE_JHYSPB  where  XQ0000 ='星期七' and SXWBZ0='1' and yxbz00='1'),

xq as

(select  NBBH00,MC0000,NBXSXH, rownumber from

(select NBBH00,MC0000,NBXSXH, row_number() over(partition by NBBH00,XQ0000,SXWBZ0 order by XSXH00)  rownumber

from VW_PE_JHYSPB

WHERE yxbz00='1')

group by NBBH00,MC0000,NBXSXH,rownumber

)

select xq.NBBH00 NBBH00,

xq.MC0000 MC0000,

xq10.YGXM00 YGXM10,

xq11.YGXM00 YGXM11,

xq20.YGXM00 YGXM20,

xq21.YGXM00 YGXM21,

xq30.YGXM00 YGXM30,

xq31.YGXM00 YGXM31,

xq40.YGXM00 YGXM40,

xq41.YGXM00 YGXM41,

xq50.YGXM00 YGXM50,

xq51.YGXM00 YGXM51,

xq60.YGXM00 YGXM60,

xq61.YGXM00 YGXM61,

xq70.YGXM00 YGXM70,

xq71.YGXM00 YGXM71

from xq

left join  xq10 on xq.NBBH00=xq10.NBBH00 and xq.rownumber=xq10.rownumber

left join  xq11 on xq.NBBH00=xq11.NBBH00 and xq.rownumber=xq11.rownumber

left join  xq20 on xq.NBBH00=xq20.NBBH00 and xq.rownumber=xq20.rownumber

left join  xq21 on xq.NBBH00=xq21.NBBH00 and xq.rownumber=xq21.rownumber

left join  xq30 on xq.NBBH00=xq30.NBBH00 and xq.rownumber=xq30.rownumber

left join  xq31 on xq.NBBH00=xq31.NBBH00 and xq.rownumber=xq31.rownumber

left join  xq40 on xq.NBBH00=xq40.NBBH00 and xq.rownumber=xq40.rownumber

left join  xq41 on xq.NBBH00=xq41.NBBH00 and xq.rownumber=xq41.rownumber

left join  xq50 on xq.NBBH00=xq50.NBBH00 and xq.rownumber=xq50.rownumber

left join  xq51 on xq.NBBH00=xq51.NBBH00 and xq.rownumber=xq51.rownumber

left join  xq60 on xq.NBBH00=xq60.NBBH00 and xq.rownumber=xq60.rownumber

left join  xq61 on xq.NBBH00=xq61.NBBH00 and xq.rownumber=xq61.rownumber

left join  xq70 on xq.NBBH00=xq70.NBBH00 and xq.rownumber=xq70.rownumber

left join  xq71 on xq.NBBH00=xq71.NBBH00 and xq.rownumber=xq71.rownumber

order by xq.NBXSXH,xq.rownumber