我写了个语句,你试试看,可以直接出 你要是写过程的话,就在我注释那里,把时间过滤下就可以了 , select e.fid,e.fnumberc,e.fname_l2c,e.fnumbera,e.fname_l2a, d.fnumber,d.fname_l2,f.fnumber, substr(f.fnumber,1,4) YY,substr(f.fnumber,5) mm,w.fnumber,w.fname_l2, c.FSAL007,c.FSAL005,c.FFSAL434 from ( select 'T_HR_SCHMHIS_1' as FHISTABLENAME, FPERSONID,upper(FPERIOD) FPERIOD, FADMINORGUNITID,FSAL029,FSAL007, 0 as FSAL005,FSAL004,FFSAL434 from T_HR_SCHMHIS_1 union all select 'T_HR_SCHMHIS_2' as FHISTABLENAME,FPERSONID,upper(FPERIOD) FPERIOD, FADMINORGUNITID,FSAL029,FSAL007, FSAL005,FSAL004,FFSAL434 from T_HR_SCHMHIS_2 ) c left join T_BD_PERSON d on c.FPERSONID=d.fid left join (select upper(fid) fid,fnumber from T_HR_CMPPERIOD --where substr(fnumber,1,4) >=基准年FbaseYear and substr(fnumber,1,4) <=比较年FcmpYear ) f on c.FPERIOD=f.fid left join T_ORG_ADMIN w on c.FADMINORGUNITID=w.fid left join (select a.fid,a.fnumber fnumberc ,a.fname_l2 fname_l2c, b.fnumber fnumbera,b.fname_l2 fname_l2a,a.FHISTABLENAME from T_HR_CMPSCHEME a left join T_ORG_ADMIN b on a.fadminorgid=b.fid) e on e.FHISTABLENAME =c.FHISTABLENAME order by e.fid
其实就是通过循环把数据放入临时表。最后输出的时候,把 这段代码 ( select 'T_HR_SCHMHIS_1' as FHISTABLENAME, FPERSONID,upper(FPERIOD) FPERIOD, FADMINORGUNITID,FSAL029,FSAL007, 0 as FSAL005,FSAL004,FFSAL434 from T_HR_SCHMHIS_1 union all select 'T_HR_SCHMHIS_2' as FHISTABLENAME,FPERSONID,upper(FPERIOD) FPERIOD, FADMINORGUNITID,FSAL029,FSAL007, FSAL005,FSAL004,FFSAL434 from T_HR_SCHMHIS_2 ) c 换成 (select * from) c 临时表就可以, 现在的问题是,你的表结构不一样,你的临时表怎么定义
根据我之前写的SQL。临时表建议如下 create table 临时表( FHISTABLENAME, FPERSONID, FPERIOD, FADMINORGUNITID, FSAL029, FSAL007, FSAL005, FSAL004, FFSAL434 ) 你通过循环取出薪酬数据表之后,就要拼接插入语句了 select wm_concat(column_name) into ww from user_tab_columns where table_name =薪酬数据表 --简单写了,这里用动态语句 这样就得到的有的列名 insert into 临时表 (ww) select ww from 薪酬数据表 这样就可以插进去临时表了,没有对用的字段都为空了。你通过循环东插入临时表了,最后把我的SQL改改就可以输出了
不太明白....可否写详细的没有啥oracle基础
select A.FID, A.FNUMBER, A.FNAME_L2, B.FNUMBER, D.FNAME_L2, B.Fname_l2, D.Fnumber, F.Fnumber, E.FNUMBER, TO_NUMBER(SUBSTR(E.FNUMBER,1,4)), TO_NUMBER(SUBSTR(E.FNUMBER,5,2)), F.Fname_l2, C.FSAL029, C.FSAL005, C.FFSAL434 from T_HR_CMPSCHEME A LEFT JOIN T_ORG_ADMIN B ON A.FAdminOrgID = B.FID LEFT JOIN ( SELECT FPERSONID,FPERIOD,FADMINORGUNITID,FSAL029,FSAL007,0 FSAL005,FSAL004,FFSAL434,'T_HR_SCHMHIS_1' fHisTableName FROM T_HR_SCHMHIS_1 TA UNION ALL SELECT FPERSONID,FPERIOD,FADMINORGUNITID,FSAL029,FSAL007,FSAL005,FSAL004,FFSAL434,'T_HR_SCHMHIS_2' fHisTableName FROM T_HR_SCHMHIS_2 TA ) C on A.fHisTableName = C.fHisTableName INNER JOIN T_BD_PERSON D ON UPPER(C.FPERSONID) = UPPER(D.FID) INNER JOIN T_HR_CMPPERIOD E ON TO_CHAR(E.FNUMBER)<201201 AND UPPER(E.FID)=UPPER(C.FPERIOD) LEFT JOIN T_ORG_ADMIN F ON F.FID = C.FADMINORGUNITID ORDER BY A.FID,D.FID
select A.FID, A.FNUMBER, A.FNAME_L2, B.FNUMBER, D.FNAME_L2, B.Fname_l2, D.Fnumber, F.Fnumber, E.FNUMBER, TO_NUMBER(SUBSTR(E.FNUMBER,1,4)), TO_NUMBER(SUBSTR(E.FNUMBER,5,2)), F.Fname_l2, C.FSAL029, C.FSAL005, C.FFSAL434 from T_HR_CMPSCHEME A LEFT JOIN T_ORG_ADMIN B ON A.FAdminOrgID = B.FID LEFT JOIN ( SELECT FPERSONID,FPERIOD,FADMINORGUNITID,FSAL029,FSAL007,0 FSAL005,FSAL004,FFSAL434,'T_HR_SCHMHIS_1' fHisTableName FROM T_HR_SCHMHIS_1 TA UNION ALL SELECT FPERSONID,FPERIOD,FADMINORGUNITID,FSAL029,FSAL007,FSAL005,FSAL004,FFSAL434,'T_HR_SCHMHIS_2' fHisTableName FROM T_HR_SCHMHIS_2 TA ) C on A.fHisTableName = C.fHisTableName INNER JOIN T_BD_PERSON D ON UPPER(C.FPERSONID) = UPPER(D.FID) INNER JOIN T_HR_CMPPERIOD E ON TO_CHAR(E.FNUMBER)<201201 AND UPPER(E.FID)=UPPER(C.FPERIOD) LEFT JOIN T_ORG_ADMIN F ON F.FID = C.FADMINORGUNITID ORDER BY A.FID,D.FID id1 cmp001 薪酬方案1 01.01.01 张三 黄金集团_一分公司_财务部 emp001 01.03.02 201011 2010 11 黄金集团_三分公司_行政部 3500 0 1 id1 cmp001 薪酬方案1 01.01.01 李四 黄金集团_一分公司_财务部 emp002 01.02.02 201108 2011 8 黄金集团_二分公司_行政部 3000 0 1 id1 cmp001 薪酬方案1 01.01.01 王五 黄金集团_一分公司_财务部 emp003 01.03.01 201108 2011 8 黄金集团_三分公司_财务部 4500 0 2 id2 cmp002 薪酬方案2 01.02.02 王五 黄金集团_二分公司_行政部 emp003 01.03.01 201011 2010 11 黄金集团_三分公司_财务部 1000 500 2 id2 cmp002 薪酬方案2 01.02.02 赵六 黄金集团_二分公司_行政部 emp004 01.03.02 201108 2011 8 黄金集团_三分公司_行政部 3400 300 1
你要是写过程的话,就在我注释那里,把时间过滤下就可以了
,
select e.fid,e.fnumberc,e.fname_l2c,e.fnumbera,e.fname_l2a,
d.fnumber,d.fname_l2,f.fnumber,
substr(f.fnumber,1,4) YY,substr(f.fnumber,5) mm,w.fnumber,w.fname_l2,
c.FSAL007,c.FSAL005,c.FFSAL434
from
(
select 'T_HR_SCHMHIS_1' as FHISTABLENAME, FPERSONID,upper(FPERIOD) FPERIOD,
FADMINORGUNITID,FSAL029,FSAL007, 0 as FSAL005,FSAL004,FFSAL434
from T_HR_SCHMHIS_1
union all
select 'T_HR_SCHMHIS_2' as FHISTABLENAME,FPERSONID,upper(FPERIOD) FPERIOD,
FADMINORGUNITID,FSAL029,FSAL007, FSAL005,FSAL004,FFSAL434
from T_HR_SCHMHIS_2
) c
left join T_BD_PERSON d on c.FPERSONID=d.fid
left join (select upper(fid) fid,fnumber
from T_HR_CMPPERIOD
--where substr(fnumber,1,4) >=基准年FbaseYear and substr(fnumber,1,4) <=比较年FcmpYear
) f on c.FPERIOD=f.fid
left join T_ORG_ADMIN w on c.FADMINORGUNITID=w.fid
left join (select a.fid,a.fnumber fnumberc ,a.fname_l2 fname_l2c,
b.fnumber fnumbera,b.fname_l2 fname_l2a,a.FHISTABLENAME
from T_HR_CMPSCHEME a
left join T_ORG_ADMIN b on a.fadminorgid=b.fid) e on e.FHISTABLENAME =c.FHISTABLENAME
order by e.fid
和 T_HR_SCHMHIS_1
不能直接引用的,这两个表是动态生成的,生成规律不知道,我这里只是写规律而已。
这两个薪酬数据表要先查出 T_HR_CMPSCHEME 的FHisTableName字段,才知道各薪酬方案的数据表名是什么,所以你的语句不能实现我的需求
必须要查薪酬方案表T_HR_CMPSCHEME的FHisTableName才知道对应薪酬方案的薪酬数据存在哪里,然后才能去查。二楼的语句全部是固态,那是行不通的,我的例子仅是最简单的通用例,我的意思是要先遍历T_HR_CMPSCHEME薪酬方案表,查出符合条件的薪酬方案,然后再找出这个方案的对应薪酬数据存在哪个表中(FhisTableName),再去这个动态生成的薪酬数据表中找到有有哪些人,有哪些薪酬项目,薪酬期间是多少。把所有薪酬方案的薪酬数据内容全部汇总成一个表输出
问题1:每个薪酬数据表格式不一样,列不一样,列名不一样,那我怎么定义临时表的结构。
就是循环插数据,也要列一样才行啊。
这段代码
(
select 'T_HR_SCHMHIS_1' as FHISTABLENAME, FPERSONID,upper(FPERIOD) FPERIOD,
FADMINORGUNITID,FSAL029,FSAL007, 0 as FSAL005,FSAL004,FFSAL434
from T_HR_SCHMHIS_1
union all
select 'T_HR_SCHMHIS_2' as FHISTABLENAME,FPERSONID,upper(FPERIOD) FPERIOD,
FADMINORGUNITID,FSAL029,FSAL007, FSAL005,FSAL004,FFSAL434
from T_HR_SCHMHIS_2
) c
换成
(select * from) c 临时表就可以,
现在的问题是,你的表结构不一样,你的临时表怎么定义
create table 临时表(
FHISTABLENAME,
FPERSONID,
FPERIOD,
FADMINORGUNITID,
FSAL029,
FSAL007,
FSAL005,
FSAL004,
FFSAL434 ) 你通过循环取出薪酬数据表之后,就要拼接插入语句了
select wm_concat(column_name) into ww from user_tab_columns where table_name =薪酬数据表
--简单写了,这里用动态语句
这样就得到的有的列名
insert into 临时表
(ww)
select ww from 薪酬数据表
这样就可以插进去临时表了,没有对用的字段都为空了。你通过循环东插入临时表了,最后把我的SQL改改就可以输出了
A.FID,
A.FNUMBER,
A.FNAME_L2,
B.FNUMBER,
D.FNAME_L2,
B.Fname_l2,
D.Fnumber,
F.Fnumber,
E.FNUMBER,
TO_NUMBER(SUBSTR(E.FNUMBER,1,4)),
TO_NUMBER(SUBSTR(E.FNUMBER,5,2)),
F.Fname_l2,
C.FSAL029,
C.FSAL005,
C.FFSAL434 from
T_HR_CMPSCHEME A LEFT JOIN T_ORG_ADMIN B ON A.FAdminOrgID = B.FID
LEFT JOIN
(
SELECT FPERSONID,FPERIOD,FADMINORGUNITID,FSAL029,FSAL007,0 FSAL005,FSAL004,FFSAL434,'T_HR_SCHMHIS_1' fHisTableName FROM T_HR_SCHMHIS_1 TA
UNION ALL
SELECT FPERSONID,FPERIOD,FADMINORGUNITID,FSAL029,FSAL007,FSAL005,FSAL004,FFSAL434,'T_HR_SCHMHIS_2' fHisTableName FROM T_HR_SCHMHIS_2 TA
) C on A.fHisTableName = C.fHisTableName
INNER JOIN
T_BD_PERSON D ON UPPER(C.FPERSONID) = UPPER(D.FID)
INNER JOIN T_HR_CMPPERIOD E ON TO_CHAR(E.FNUMBER)<201201 AND UPPER(E.FID)=UPPER(C.FPERIOD)
LEFT JOIN T_ORG_ADMIN F ON F.FID = C.FADMINORGUNITID
ORDER BY A.FID,D.FID
A.FID,
A.FNUMBER,
A.FNAME_L2,
B.FNUMBER,
D.FNAME_L2,
B.Fname_l2,
D.Fnumber,
F.Fnumber,
E.FNUMBER,
TO_NUMBER(SUBSTR(E.FNUMBER,1,4)),
TO_NUMBER(SUBSTR(E.FNUMBER,5,2)),
F.Fname_l2,
C.FSAL029,
C.FSAL005,
C.FFSAL434 from
T_HR_CMPSCHEME A LEFT JOIN T_ORG_ADMIN B ON A.FAdminOrgID = B.FID
LEFT JOIN
(
SELECT FPERSONID,FPERIOD,FADMINORGUNITID,FSAL029,FSAL007,0 FSAL005,FSAL004,FFSAL434,'T_HR_SCHMHIS_1' fHisTableName FROM T_HR_SCHMHIS_1 TA
UNION ALL
SELECT FPERSONID,FPERIOD,FADMINORGUNITID,FSAL029,FSAL007,FSAL005,FSAL004,FFSAL434,'T_HR_SCHMHIS_2' fHisTableName FROM T_HR_SCHMHIS_2 TA
) C on A.fHisTableName = C.fHisTableName
INNER JOIN
T_BD_PERSON D ON UPPER(C.FPERSONID) = UPPER(D.FID)
INNER JOIN T_HR_CMPPERIOD E ON TO_CHAR(E.FNUMBER)<201201 AND UPPER(E.FID)=UPPER(C.FPERIOD)
LEFT JOIN T_ORG_ADMIN F ON F.FID = C.FADMINORGUNITID
ORDER BY A.FID,D.FID
id1 cmp001 薪酬方案1 01.01.01 张三 黄金集团_一分公司_财务部 emp001 01.03.02 201011 2010 11 黄金集团_三分公司_行政部 3500 0 1
id1 cmp001 薪酬方案1 01.01.01 李四 黄金集团_一分公司_财务部 emp002 01.02.02 201108 2011 8 黄金集团_二分公司_行政部 3000 0 1
id1 cmp001 薪酬方案1 01.01.01 王五 黄金集团_一分公司_财务部 emp003 01.03.01 201108 2011 8 黄金集团_三分公司_财务部 4500 0 2
id2 cmp002 薪酬方案2 01.02.02 王五 黄金集团_二分公司_行政部 emp003 01.03.01 201011 2010 11 黄金集团_三分公司_财务部 1000 500 2
id2 cmp002 薪酬方案2 01.02.02 赵六 黄金集团_二分公司_行政部 emp004 01.03.02 201108 2011 8 黄金集团_三分公司_行政部 3400 300 1
而且数据结构并不规律,不一样的....