使相关的子查询超出CASE WHEN语句

Question

我在Oracle中有一个employee表，它可以在表future_jobs中有一个或两个“将来”作业，这是某种商业规则（例如，

| employee_id | job_id | job_start_date | job_end_date | 
|-------------|--------|----------------|--------------| 
| 1   | 127589 | 12-SEP-2016 | 25-DEC-2016 | 
| 1   | 834780 | 26-DEC-2016 | 08-AUG-2017 | 
| 2   | 800253 | 20-OCT-2016 | 13-APR-2017 | 

我必须通过调用具有特定参数的存储过程来获得每个未来作业的描述，例如， F1和F2，基于job_start_date的降序排列。在上面的例子中，对于employee_id = 1，当针对job_id = 127589行执行以下查询时，因为job_start_date = 12-SEP-2016是employee_id = 1的两行中最早的日期，应调用get_description(emp.employee_id, 'F1')，get_description(emp.employee_id, 'F2')为job_id = 834780。

和employee_id = 2，由于只有一个未来作业，因此应使用以下查询调用get_description(emp.employee_id, 'F1')。目前，我可以通过以下查询来获取相关信息：

select 
    emp.employee_id, 
    case 
     when fj.job_start_date = (select max(job_start_date) 
            from future_jobs 
            where employee_id = fj.employee_id 
            group by employee_id 
            having count(employee_id) > 1) 
     then get_description(emp.employee_id, 'F2') 
     else get_description(emp.employee_id, 'F1') 
    end job_description, 
    fj.job_start_date 
    jd.some_additional_columns 
from employees emp 
join future_jobs fj 
    on emp.employee_id = fj.employee_id 
join job_details jd 
    on jd.job_id = fj.job_id 
    and jd.job_start_date = fj.job_start_date 
    and jd.job_end_date = fj.job_end_date 

。

| employee_id | job_description | job_start_date | jd.columns | 
|-------------|----------------------|----------------|--------------| 
| 1   | 1st future job desc | 12-SEP-2016 | ....   | 
| 1   | 2nd future job desc | 26-DEC-2016 | ....   | 
| 2   | 1st future job desc | 20-OCT-2016 | ....   | 

但是，我想知道是否有另一种方法来取出CASE WHEN语句的相关子查询吗？有没有办法做到这一点，而不使用相关的子查询？我需要在一个声明中完成此操作，而不是使用WITH子句类型的解决方案。

Answer 1

我觉得你只是想窗函数：

select emp.employee_id, 
     (case when fj.seqnum = 1 
      then get_description(emp.employee_id, 'F1') 
      else get_description(emp.employee_id, 'F2') 
     end) as job_description, 
     jd.some_additional_columns 
from employees emp join 
    (select fj.*, 
      row_number() over (partition by employee_id order by fj.job_start_date) as seqnum 
     from future_jobs fj 
    ) fj 
    on emp.employee_id = fj.employee_id join 
    job_details jd 
    on jd.job_id = fj.job_id and 
     jd.job_start_date = fj.job_start_date and 
     jd.job_end_date = fj.job_end_date; 

我不是100％肯定的逻辑是完全正确的。它遵循你的描述，并使用F1为第一个未来的工作。

Answer 2

实际上，第二个想法是，你甚至不需要最大开始日期，并且你不需要嵌套select来获得一个行号，你可以在case语句中用count（*）作为窗口函数。

select 
    emp.employee_id, 
    case 
     when COUNT(*) OVER (PARTITION BY fj.employee_id ORDER BY fj.job_start_date) > 1 
     then get_description(emp.employee_id, 'F2') 
     else get_description(emp.employee_id, 'F1') 
    end job_description, 
    jd.some_additional_columns 
from 
    employees emp 
    join future_jobs fj 
    on emp.employee_id = fj.employee_id 
    join job_details jd 
    on jd.job_id = fj.job_id 
    and jd.job_start_date = fj.job_start_date 
    and jd.job_end_date = fj.job_end_date 

我喜欢戈登想的窗口功能，但我使用MAX（）和COUNT（）在测试你的子查询的条件。但是和他一样，我并不积极，我完全理解你想要的逻辑。

select 
    emp.employee_id, 
    case 
     when fj.job_start_date = MAX(fj.job_start_date) OVER (PARTITION BY fj.employee_id) 
      AND COUNT(*) OVER (PARTITION BY fj.employee_id) > 1 
     then get_description(emp.employee_id, 'F2') 
     else get_description(emp.employee_id, 'F1') 
    end job_description, 
    jd.some_additional_columns 
from 
    employees emp 
    join future_jobs fj 
    on emp.employee_id = fj.employee_id 
    join job_details jd 
    on jd.job_id = fj.job_id 
    and jd.job_start_date = fj.job_start_date 
    and jd.job_end_date = fj.job_end_date 

运行计数示例

DECLARE @Table AS TABLE (A CHAR(1),P INT) 
INSERT INTO @Table (A,P) VALUES ('A',1),('B',1),('C',2),('D',2) 

SELECT 
    * 
    ,COUNT(*) OVER (PARTITION BY P ORDER BY A) as RunningCount 
FROM 
    @Table