我有此查询(完美运行):
WITH TEMPTABLE AS(
SELECT count(CONTRACT_ID) AS no_contract, T.BRANCH_ID as branch
FROM CONTRACT, "EMPLOYMENT" T, TABLE(T."EMPLOYEE") TT
WHERE CONTRACT.EMPLOYEE_ID = TT.EMPLOYEE_ID
AND CONTRACT.CONTRACT_VT_START BETWEEN '01/01/2018' AND '30/06/2018'
GROUP BY T.BRANCH_ID
)
SELECT branch
FROM TEMPTABLE
WHERE no_contract IN (SELECT MAX(no_contract)
FROM TEMPTABLE);
我想知道它是否可以通过另一种方式而不是子句来完成。这样的东西:
SELECT count_per_branch.BRANCH
FROM (SELECT count(CONTRACT_ID) AS no_contract, BRANCH_ID AS BRANCH
FROM CONTRACT, "EMPLOYMENT" T, TABLE(T."EMPLOYEE") TT
WHERE CONTRACT.EMPLOYEE_ID = TT.EMPLOYEE_ID
AND CONTRACT.CONTRACT_VT_START BETWEEN '01/01/2018' AND '30/06/2018'
GROUP BY T.BRANCH_ID) count_per_branch
WHERE count_per_branch.no_contract =
(SELECT max(count_per_branch.no_contract)
FROM count_per_branch);
给出一个ORA-00942:"不存在表或视图"线路错误:9
您可以在从子句中使用子查询:
FROM (SELECT count(CONTRACT_ID) ... )
您可以给它一个别名:
FROM (SELECT count(CONTRACT_ID) ... ) count_per_branch
您可以使用该别名来限定列参考:
WHERE count_per_branch.no_contract =
但是您不能使用该别名作为另一个子查询中的行源,这是您在这里尝试做的:
(SELECT max(count_per_branch.no_contract)
FROM count_per_branch)
要以这种方式"重用"子查询,您必须使用常见的表表达语法 - 即,with with with strause。