gpt4 book ai didi

oracle - 在 Oracle 的查询中不能两次引用 CTE 吗?

转载 作者:行者123 更新时间:2023-12-01 09:06:37 26 4
gpt4 key购买 nike

我有一个 CTE 链,最后我想从最后一个中选择两次。 Oracle 允许我做任何一个选择,但不能同时做两个选择(即使我做“select * from (select union select)”。我唯一可以缩小问题范围的就是在两个中引用“runinfo”选择,但这本身并不是香农所显示的问题。

WITH lastTen AS (
SELECT id
FROM (SELECT autobuild_id, id, rank() OVER (PARTITION BY autobuild_id ORDER BY id DESC) as rank
FROM runs
WHERE status='FINISHED' AND type='FULL' AND build_failed in ('n', 'N'))
WHERE rank <= 10
),

recentAvg AS (
SELECT autobuild_id, avg(elapsed) avgtime
FROM runs
JOIN lastTen ON (runs.id = lastTen.id)
GROUP BY autobuild_id
),

runinfo AS (
SELECT autobuildid, runid, changelist, status, age
FROM (
SELECT runs.autobuild_id autobuildid, runs.id runid, changelist, runs.status status, runs.create_date, a.avgtime,
CASE WHEN status = 'RUNNING' THEN TO_NUMBER(sysdate - start_date)*86400 -- in seconds to compare to elapsed
WHEN status = 'TO BE' THEN TO_NUMBER(sysdate - create_date) -- in days
END AS age
FROM runs
LEFT JOIN recentAvg a ON (runs.autobuild_id = a.autobuild_id)
)
WHERE (status = 'RUNNING' AND age > avgtime * 1.5)
OR (status = 'TO BE' AND age > 1)
ORDER BY autobuildid, runid DESC
),

running AS (
SELECT autobuilds.id, name, runid, changelist, runinfo.status, age
FROM runinfo
JOIN autobuilds ON (runinfo.autobuildId=autobuilds.id)
WHERE runinfo.status='RUNNING'
),

tobe AS (
SELECT autobuildid, name, runid, changelist, status, age
FROM (SELECT autobuildid, name, runid, changelist, runinfo.status, age, RANK() OVER (PARTITION BY autobuildid ORDER BY runid DESC) AS rank
FROM runinfo
JOIN autobuilds ON (runinfo.autobuildid=autobuilds.id)
WHERE runinfo.status='TO BE')
WHERE rank=1
)

SELECT * FROM running
UNION ALL
SELECT * FROM tobe

最佳答案

最简单的查询对您不起作用?

完整的错误信息是什么?

Oracle 确实允许 CTE 被引用两次,但我不明白您在尝试什么:

SQL> with a as (select * from dual)
2 , b as (select * from a)
3 , c as (select * from b)
4 select *
5 from b, c
6 where b.dummy = c.dummy;

D D
- -
X X

关于oracle - 在 Oracle 的查询中不能两次引用 CTE 吗?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6687406/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com