如何在蟾蜍数据点查询生成器中保持连接状态?
问题描述:
我想在蟾蜍数据点中建立一个查询。我有一个子查询,它有一个行号来标识我感兴趣的记录。只有在行号为1时,此子查询才需要留在主表中。以下是我想要查看的查询:如何在蟾蜍数据点查询生成器中保持连接状态?
SELECT distinct E.EMPLID, E.ACAD_CAREER
FROM PS_STDNT_ENRL E
LEFT JOIN (
SELECT ACAD_CAREER, ROW_NUMBER() OVER (PARTITION BY ACAD_CAREER ORDER BY EFFDT DESC) as RN
FROM PS_ACAD_CAR_TBL
) T on T.ACAD_CAREER = E.ACAD_CAREER and RN = 1
当我尝试复制它时,行号条件放置在全局WHERE子句中。这不是预期的功能,因为它会删除子查询中没有匹配的记录,从而使其成为内部联接。
这里是它生成查询:
SELECT DISTINCT E.EMPLID, E.ACAD_CAREER, T.RN
FROM SYSADM.PS_STDNT_ENRL E
LEFT OUTER JOIN
(SELECT PS_ACAD_CAR_TBL.ACAD_CAREER,
ROW_NUMBER()
OVER (PARTITION BY ACAD_CAREER ORDER BY EFFDT DESC)
AS RN
FROM SYSADM.PS_ACAD_CAR_TBL PS_ACAD_CAR_TBL) T
ON (E.ACAD_CAREER = T.ACAD_CAREER)
WHERE (T.RN = 1)
有没有办法让查询生成器放置该行号条件对左连接,而不是全球WHERE子句?
答
我找到了一种方法来使这个工作。
- 计算字段添加到主表的1
- 加入行号这一新计算字段。
现在查询在连接条件的过滤器,而不是在WHERE子句,以便它任命为意。下面是它的查询:
SELECT DISTINCT E.EMPLID, E.ACAD_CAREER, T.RN
FROM SYSADM.PS_STDNT_ENRL E
LEFT OUTER JOIN
(SELECT PS_ACAD_CAR_TBL.ACAD_CAREER,
ROW_NUMBER()
OVER (PARTITION BY ACAD_CAREER ORDER BY EFFDT DESC)
AS RN
FROM SYSADM.PS_ACAD_CAR_TBL PS_ACAD_CAR_TBL) T
ON (E.ACAD_CAREER = T.ACAD_CAREER) AND (1 = T.RN)