代码之家 › 专栏 › 技术社区 › jitbit

SQL查询帮助-任务排序、分组和状态+日期条件问题

oracle sql

jitbit · 技术社区 · 16 年前

希望我能公正地处理这个问题,因为很难在标题中总结出来!(欢迎在评论中提出建议)

好的,这是我的桌子:

Tasks
  task_id        (number)
  job_id         (number)
  to_do_by_date  (date)
  task_name      (varchar / text)
  status         (number)
  completed_date (date)

为了便于论证,让我们将状态值设为:

1 = New
2 = InProgress
3 = Done

我尝试做的就是创建一个查询来收回所有的任务:

其中的任何任务 job_id 有一个 status 完成;
- 除非所有任务 乔布斯 已完成,但一个或多个任务具有 completed_date 今天
按顺序排序 to_be_done_by 日期,但将所有作业ID任务分组在一起
- 所以 乔布斯 下一个“按日期做”任务首先显示

有关数据的一些信息:

一 乔布斯 可以有任意数量的任务

下面是我尝试获得的输出示例:

task_id   job_id   to_do_by_date   task_name   status   completed_date
   1        1        yesterday        -          3        yesterday
   2        1        today            -          3        today
   3        2        now              -          3        today
   4        2        2 hours time     -          2        {null}
   5        2        4 hours time     -          2        {null}
   6        2        tomorrow         -          1        {null}
   7        3        3 hours time     -          2        {null}
   8        3        tomorrow         -          1        {null}
   9        3        tomorrow         -          1        {null}

我使用的是Oracle10g,所以对于Oracle或AnsiSQL的答案,或者关于如何实现这一点的提示是理想的,我可以创建视图或将其包装在存储过程中,以便在解决方案需要时从应用程序中卸载逻辑。

下面是一个SQL脚本,它将创建上面显示的示例测试数据:

create table tasks (task_id number, job_id number, to_do_by_date date, task_name varchar2(50), status number, completed_date date);
insert into tasks values (0,0,sysdate -2,    'Job 0, Task 1 - dont return!', 3, sysdate -2);
insert into tasks values (1,1,sysdate -1,    'Job 1, Task 1', 3, sysdate -1);
insert into tasks values (2,1,sysdate -2/24, 'Job 1, Task 2', 3, sysdate -2/24);
insert into tasks values (3,2,sysdate,       'Job 2, Task 1', 3, sysdate);
insert into tasks values (4,2,sysdate +2/24, 'Job 2, Task 2', 2, null);
insert into tasks values (5,2,sysdate +4/24, 'Job 2, Task 3', 2, null);
insert into tasks values (6,2,sysdate +1,    'Job 2, Task 4', 1, null);
insert into tasks values (7,3,sysdate +3/24, 'Job 3, Task 1', 2, null);
insert into tasks values (8,3,sysdate +1,    'Job 3, Task 2', 1, null);
insert into tasks values (9,3,sysdate +1,    'Job 3, Task 3', 1, null);
commit;

非常感谢您的帮助:o)

4 回复 | 直到 11 年前

DJ. 16 年前

很明显,你得把这个修好一点,但我希望你能明白。

SELECT 
    task_id, job_id, to_do_by_date, task_name, status, completed_date
FROM
    Tasks
WHERE
    job_id IN (
        SELECT job_id 
        FROM Tasks 
        WHERE status <> 'Done' 
        GROUP BY job_id)
    OR
    job_id IN (
        SELECT job_id 
        FROM Tasks 
        WHERE status = 'Done' AND completed_date = 'Today'
            AND job_id NOT IN (SELECT job_id FROM Tasks WHERE status <> 'Done' GROUP BY job_id)
        GROUP BY job_id)
ORDER BY
    job_id, to_do_by_date

David Aldridge 16 年前

我同意贾斯廷的观点——我不明白为什么会有2人回来。

下面是一个使用分析函数的查询,根据逻辑描述返回正确的行。

select * from
(
select t.*,
       min(status) over (partition by job_id) min_status_over_job,
       max(status) over (partition by job_id) max_status_over_job,
       sum(case when trunc(completed_date) = trunc(sysdate)-1 then 1 else 0 end) 
                   over (partition by job_id) num_complete_yest
from   tasks t
)
where  max_status_over_job < 3
       or (min_status_over_job = 3 and num_complete_yest > 0)
/

Justin Cave 16 年前

考虑到你的要求,我不明白为什么在你的结果中应该返回工作ID 2。有一个任务的状态为“完成”,因此它不符合第一个条件

一份工作的所有任务都有状态完成

还有一些任务的状态不是“完成”,所以它不符合第二个标准

除了一个作业的所有任务已完成,但有一个或多个任务完成今天的日期

是否还有其他原因需要包括作业ID=2?

SQL> ed
Wrote file afiedt.buf

  1  select task_id, job_id, to_do_by_date, task_name, status, completed_date
  2    from tasks t1
  3   where not exists( select 1
  4                       from tasks t2
  5                      where t1.job_id = t2.job_id
  6                        and t2.status  = 3)
  7      or ((not exists( select 1
  8                        from tasks t3
  9                       where t1.job_id  = t3.job_id
 10                         and t3.status != 3))
 11          and
 12          exists (select 1
 13                    from tasks t4
 14                   where t1.job_id = t4.job_id
 15                     and trunc(t4.completed_date) = trunc(sysdate)))
 16*   order by job_id, to_do_by_date
SQL> /

   TASK_ID     JOB_ID TO_DO_BY_ TASK_NAME           STATUS COMPLETED
---------- ---------- --------- --------------- ---------- ---------
         1          1 28-OCT-08 Job 1, Task 1            3 28-OCT-08
         2          1 29-OCT-08 Job 1, Task 2            3 29-OCT-08
         7          3 29-OCT-08 Job 3, Task 1            2
         8          3 30-OCT-08 Job 3, Task 2            1
         9          3 30-OCT-08 Job 3, Task 3            1

Mark Brackett Achilles Ram Nakirekanti 16 年前

我不做Oracle,也没有现成的SQL服务器——但这会让您非常接近。

SELECT Tasks.*
FROM Tasks
JOIN (
   --Undone
   SELECT Job_Id
   FROM Tasks
   WHERE
     Status <> 3
   UNION
   --Done today
   SELECT Job_Id
   FROM Tasks
   WHERE
     Status = 3
     AND Completed_Date = TODAY()
) as UndoneOrDoneToday ON
   Tasks.Job_Id = UndoneOrDoneToday.Job_Id
JOIN (
   SELECT Job_Id, MIN(to_do_by_date) as NextToDoByDate
   FROM Tasks
   GROUP BY Job_id
) as NextJob ON
   Tasks.Job_Id = NextJob.Job_id
ORDER BY
   NextJob.NextToDoByDate, 
   Tasks.Job_Id, --If NextToDoByDate isn't unique, this should order jobs together
   Tasks.to_do_by_date, --This may not be needed, but would put eg., task 7 due today higher than task 6 due tomorrow
   Tasks.Task_Id --this should be last

编辑:大多数其他答案似乎是按工作ID排序的。对于示例数据来说,这似乎有效,但不满足以下要求:

按“待完成”按日期排序,但将所有作业ID任务分组在一起因此,首先显示“按日期”任务旁边的作业ID