oracle – Netezza中的子查询

发布时间：2020-12-12 16:24:30 所属栏目：百科来源：网络整理

导读：我正在尝试将SQL 11从Oracle 11 G移植到Netezza. 这是我在Oracle中的查询： SELECT ID,TEACHERID,CLASS,SECTION,MAJOR,SUPERVISOR,COURSE,SCORE,SCOREDATE,(select SCORE from STUDENT_SCORES d2 where d2.ID=d1.ID and d2.SCOREDATE (d1.SCOREDATE)-28 and

我正在尝试将SQL 11从Oracle 11 G移植到Netezza.

这是我在Oracle中的查询：

SELECT
    ID,TEACHERID,CLASS,SECTION,MAJOR,SUPERVISOR,COURSE,SCORE,SCOREDATE,(select SCORE from STUDENT_SCORES d2 where d2.ID=d1.ID and d2.SCOREDATE (d1.SCOREDATE)-28 and d1.COURSE=d2.COURSE)  as PRIORSCORE,(d1.SCOREDATE)-28 as PRIORSCOREDT,REMCD
FROM 
    STUDENT_SCORES d1
WHERE 
    TEACHERID='T1' AND SCOREDATE=(SELECT MAX(SCOREDATE) FROM STUDENT_SCORES WHERE TEACHERID='T1')

当我在Netezza中运行它时出现错误：

ERROR:  (2) This form of correlated query is not supported - consider rewriting

如何为Netezza重写此查询？请帮忙.

我建议你重写代码：

select d1.ID,d1.TEACHERID,d1.CLASS,d1.SECTION,d1.MAJOR,d1.SUPERVISOR,d1.COURSE,d1.SCORE,d1.SCOREDATE,d2.SCORE as PRIORSCORE,d2.SCOREDATE as PRIORSCOREDT,d1.REMCD
from STUDENT_SCORES d1 left outer join
(select * from STUDENT_SCORES ss where ss.SCOREDATE = (SELECT MAX(SCOREDATE)-28 FROM STUDENT_SCORES WHERE TEACHERID='T1') d2
on  d1.ID=d2.ID
and d1.COURSE=d2.COURSE
where d1.TEACHERID='T1'
and d1.SCOREDATE = (SELECT MAX(SCOREDATE) FROM STUDENT_SCORES WHERE TEACHERID='T1')

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!