-- 把你视图的创建语句贴出来,就明白啦: select dbms_metadata.get_ddl('VIEW','YOUR_VIEW_NAME') from dual;
CREATE OR REPLACE FORCE VIEW "RLZY"."VIEW_FOR_WANGMAOXIN" ("EMP_ID", "EMP_NAME", "EMP_SOCIETY_ID", "NAME_SPELL", "EMP_SEX", "MOBILE_PHONE", "E_MAIL", "FAM_PHONE", "STA_CODE", "CODE_NAME", "DEPT_ID", "DEPT_NAME", "OFFICE_ID", "OFFICE_NAME", "DUTY_JOB_ID", "DUTY_JOB_NAME", "DUTY_JOB_MEMO", "VALUE", "LABEL", "USER_TYPE") AS select a.emp_id,a.emp_name,a.emp_society_id,a.name_spell,a.emp_sex,a.mobile_phone,a.e_mail,a.fam_phone,a.sta_code,g.code_name,c.dept_id,c.dept_name,b.office_id,b.office_name,d.duty_job_id,d.duty_job_name,d.duty_job_memo,e.value,e.label,f.user_type from rs_emp_nature_attrib a left join rs_section_office b on a.group_id=b.office_id left join rs_dept c on a.dept_id=c.dept_id left join rs_duty_job d on a.emp_post = d.duty_job_id left join (select code_id value,code_name label,'' filter from rs_code where base_id=350000 and use_flag='1') e on e.value = a.leader_degree left join (select code_id ,code_name from rs_code where base_id=390000 and use_flag='1') g on g.code_id= a.sta_code inner join (select dept_id,'gf' user_type from rs_dept CONNECT BY upper_dept_id = PRIOR dept_id start with upper_dept_id='2' and use_flag='Y' union select dept_id,'jt' user_type from rs_dept CONNECT BY upper_dept_id = PRIOR dept_id start with upper_dept_id='235' and use_flag='Y') f on a.dept_id=f.dept_id order by c.dept_id,b.office_id 这是视图代码
回答楼上的问题:如果单位在一个月之内调二十几个人是可能的,但一天之内是没有的事,因为我知道一天之内没有调这么多人。问题是我在第一遍查询这个视图时是2920个人,时隔几分钟,再查一次就是2906个人了。但一个月前可能是2920个人。这个视图我一个月前查询过 还有就是,我查这个视图时用了不同的写法,第一遍时是 select count(*) from myview 第二遍时是 select count(*) from rlzy.myview rlzy这个用户就是我第一次查询时进去用的用户,只有过第一次查询时没有写上。 我用的是PLSQL Developer
再就是我有十多年的SQLSERVER数据库维护经验,我不明白的是可能ORACLE是不是在视图这个概念上和SQLSERVER不一样
select dbms_metadata.get_ddl('VIEW','YOUR_VIEW_NAME') from dual;
select a.emp_id,a.emp_name,a.emp_society_id,a.name_spell,a.emp_sex,a.mobile_phone,a.e_mail,a.fam_phone,a.sta_code,g.code_name,c.dept_id,c.dept_name,b.office_id,b.office_name,d.duty_job_id,d.duty_job_name,d.duty_job_memo,e.value,e.label,f.user_type
from rs_emp_nature_attrib a
left join rs_section_office b on a.group_id=b.office_id
left join rs_dept c on a.dept_id=c.dept_id
left join rs_duty_job d on a.emp_post = d.duty_job_id
left join (select code_id value,code_name label,'' filter from rs_code where base_id=350000 and use_flag='1') e on e.value = a.leader_degree
left join (select code_id ,code_name from rs_code where base_id=390000 and use_flag='1') g on g.code_id= a.sta_code
inner join (select dept_id,'gf' user_type from rs_dept CONNECT BY upper_dept_id = PRIOR dept_id start with upper_dept_id='2' and use_flag='Y'
union select dept_id,'jt' user_type from rs_dept CONNECT BY upper_dept_id = PRIOR dept_id start with upper_dept_id='235' and use_flag='Y') f on a.dept_id=f.dept_id
order by c.dept_id,b.office_id
这是视图代码
还有就是,我查这个视图时用了不同的写法,第一遍时是
select count(*) from myview
第二遍时是
select count(*) from rlzy.myview
rlzy这个用户就是我第一次查询时进去用的用户,只有过第一次查询时没有写上。
我用的是PLSQL Developer
按理说基本不动,view不会变动~
期待楼主解决