查询sql

原创于 2011-02-23 17:05:55 发布 · 105 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #C #C++ #C#

数据库专栏收录该内容

16 篇文章

订阅专栏

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

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)