-- 想要达到的结果 取EZOFFICE.wf_proceedActivity的wf_proceedactivity_id列最新的一段数值合并到EZOFFICE.wf_transition表合符条件的结果集中.
select ezoffice.hibernate_sequence.nextval,r.transitionFrom, r.transitionName,r.transitionTo,r.transitionDescription,b.wf_proceedactivity_id,0,r.expression,nvl(r.defaultactivity,0)
from EZOFFICE.wf_transition r,
(select a.wf_activity_id,a.wf_proceedactivity_id,a.wf_workflowprocess_id,a.employee_id,
row_number() over(partition by wf_activity_id, wf_workflowprocess_id, employee_id order by wf_proceedactivity_id desc) rn
from EZOFFICE.wf_proceedActivity a) b
where r.transitionfrom in (select act.wf_activity_id from ezoffice.wf_activity act where act.wf_workflowprocess_id = 32226)
and b.rn = 1;--EZOFFICE.wf_proceedActivity表和EZOFFICE.wf_transition中没有可以匹配相等的列.select a.wf_activity_id,a.wf_proceedactivity_id,a.wf_workflowprocess_id,a.employee_id,
row_number() over(partition by wf_activity_id, wf_workflowprocess_id, employee_id order by wf_proceedactivity_id desc) rn
from EZOFFICE.wf_proceedActivity a--这段查询出的结果是13列.select ezoffice.hibernate_sequence.nextval,r.transitionFrom, r.transitionName,r.transitionTo,r.transitionDescription,r.expression,nvl(r.defaultactivity,0)
from EZOFFICE.wf_transition r where r.transitionfrom in (select act.wf_activity_id from ezoffice.wf_activity act where act.wf_workflowprocess_id = 32226);--这段查询出的结果也是13行.
--但是和在一起查出的是169行,被笛卡尔乘积了.-- 是不是要改成视图,若是不用视图,该怎么改,请各位高人指教.
解决方案 »
- oracle11g,如何创建临时目录,新人求助
- oracle知识(英语不好帮下忙给下答案和理由)
- 请教高手们一个SQL怎么写
- oracle表导出到acess有什么好办法吗?
- 官方网站下的Oracle有限制吗?
- 全文检索(CONTAINS)的问题?
- ora-01034错误,来都有分。
- 本人初次接触orcle,问一个很弱的问题:如何在vc6下通过OO4O访问orcle ?
- 我没有oracle8.1.6客户端?需要?需要?需要?
- jdbc 调用oracle 的procedure ,查询的结果以游标返回,抛出no more data from read socket。
- 怎么根据条件UPDATE 两个以上字段?
- oracle10g倒入dmp文件问题,小弟求各位哥哥们帮帮我啊~
1表有a b c列 2表有 d e f g怎么把d从2中取出放到1中 a d c d e f g 都是不相等的列.
产生的结果是这样的,d列的每一行都和1表中的数据乘了一边 即,d中每个相等的值都有N(N=1表的行数)个
FROM ((SELECT A.*, ROWNUM AR FROM WF_PROCEEDACTIVITY A) X,
(SELECT B.*, ROWNUM BR FROM WF_TRANSITION B) Y)
WHERE X.AR = Y.BR;
2: 用GROUP BY + MAX()
FROM ((SELECT A.*, ROWNUM AR FROM WF_PROCEEDACTIVITY A) X,
(SELECT B.*, ROWNUM BR FROM WF_TRANSITION B) Y)
WHERE X.AR = Y.BR;用这段也是一样, X后面缺失右括号.
SELECT *
FROM (SELECT A.*, ROWNUM AR FROM WF_PROCEEDACTIVITY A) X,
(SELECT B.*, ROWNUM BR FROM WF_TRANSITION B) Y
WHERE X.AR = Y.BR;
FROM (SELECT A.*, ROWNUM AR FROM WF_PROCEEDACTIVITY A) X,
(SELECT B.*, ROWNUM BR FROM WF_TRANSITION B) Y
WHERE X.AR = Y.BR;