查询sql

iteye_17980

于 2011-02-23 17:05:55 发布

阅读量96

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： SQL C C++ C#

本文链接：https://blog.youkuaiyun.com/iteye_17980/article/details/82009704

数据库专栏收录该内容

16 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询用于从多个关联表中筛选符合条件的任务ID，并排除那些其前置任务未完成的任务。通过使用子查询、窗口函数ROW_NUMBER()及PARTITION BY等高级SQL特性，实现了对任务状态的有效管理和调度。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT TASK_ID
FROM (SELECT TASK_ID, PRE_TASK_ID
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY T.TASK_ID ORDER BY T.target_level) R_ID
FROM (select V.*, r.target_id, C.target_level
from v_pipe_task v,
PIPE_TASK_TARGET r,
PIPE_TARGET_CONFIG c
where v.TASK_ID = r.task_ID
AND R.TARGET_ID = C.TARGET_ID) T) A
WHERE A.R_ID = 1
AND status = 1
AND run_status = 1
AND next_time < sysdate
order by next_time, TARGET_LEVEL) TASK_TABLE
WHERE PRE_TASK_ID NOT IN
(SELECT TASK_ID
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY T.TASK_ID ORDER BY T.target_level) R_ID
FROM (select V.*, r.target_id, C.target_level
from v_pipe_task v,
PIPE_TASK_TARGET r,
PIPE_TARGET_CONFIG c
where v.TASK_ID = r.task_ID
AND R.TARGET_ID = C.TARGET_ID) T) A
WHERE A.R_ID = 1
AND status = 1
AND run_status = 1
AND next_time < sysdate)