选择相关行仅具有不可匹配的年份的行。

Question

我有一个PROJECTS表和一个PROJECT_FINANCES表：

with 
projects (project_id, year_construction) as (
select 1, 2022 from dual union all
select 2, 2023 from dual union all
select 3, 2024 from dual union all
select 4, 2025 from dual
),
project_finances (project_id, year_funding) as (
select 1, 2022 from dual union all
select 2, 2022 from dual union all
select 2, 2023 from dual union all
select 3, 2025 from dual
)
select
    *
from
    projects

PROJECTS:

PROJECT_ID YEAR_CONSTRUCTION
---------- -----------------
         1              2022
         2              2023
         3              2024
         4              2025

PROJECT_FINANCES:

PROJECT_ID YEAR_FUNDING
---------- ------------
         1         2022

         2         2022
         2         2023

         3         2025

我希望选择PROJECTS，其中PROJECT_FINANCES中的相关行只有与父项目年不匹配的年份。

例如，PROJECT 3; 2024有一个相关的项目财务记录PROJECT 3; 2025。因此，是与相关的行，但这些行的年份都与父项目的年份不匹配。所以我想选择那个项目。

我不想选择PROJECT 4，因为它没有任何相关的行。

结果如下所示：

PROJECT_ID YEAR_CONSTRUCTION
---------- -----------------
         3              2024

我如何使用Oracle 18c SQL来做到这一点？

编辑:我的首要任务是简洁，我不太担心性能。

Answer 1

加入表并在HAVING子句中使用带有条件的聚合：

SELECT p.project_id, p.year_construction
FROM projects p INNER JOIN project_finances f
ON f.project_id = p.project_id
GROUP BY p.project_id, p.year_construction
HAVING COUNT(CASE WHEN p.year_construction = f.year_funding THEN 1 END) = 0;

见演示。

Answer 2

谢谢你的CTE！

怎么样

• 联接，这将只获取匹配的项目，然后
• 减去set运算符，找出哪些匹配的项目没有匹配的年份？

SQL> with
  2  projects (project_id, year_construction) as (
  3  select 1, 2022 from dual union all
  4  select 2, 2023 from dual union all
  5  select 3, 2024 from dual union all
  6  select 4, 2025 from dual
  7  ),
  8  project_finances (project_id, year_funding) as (
  9  select 1, 2022 from dual union all
 10  select 2, 2022 from dual union all
 11  select 2, 2023 from dual union all
 12  select 3, 2025 from dual
 13  )
 14  select a.*
 15  from projects a join project_finances b on a.project_id = b.project_id
 16  minus
 17  select * from project_finances;

PROJECT_ID YEAR_CONSTRUCTION
---------- -----------------
         3              2024

SQL>

如果您想要获得project_id = 4，就会更简单，因为--从技术上讲--它在project_finances中实际上没有匹配的年份(或ID)。

 14  select * from projects
 15  minus
 16  select * from project_finances;

PROJECT_ID YEAR_CONSTRUCTION
---------- -----------------
         3              2024
         4              2025

SQL>