选择相关行只有不匹配年份的行

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

您可以使用EXISTS，并在子查询中聚合并使用HAVING子句和条件聚合来检查相应年份是否存在：

select *
from   projects p
WHERE  EXISTS(
SELECT 1
FROM   project_finances f
GROUP BY f.project_id
HAVING COUNT(CASE f.year_funding WHEN p.year_construction THEN 1 END) = 0
AND    p.project_id = f.project_id
)

对于样本数据，输出：

PROJECT_ID	YEAR_CONSTRUCTION
3	2024

连接表并使用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;

请参阅演示

感谢CTE！

---

怎么样

• JOIN，它将只获取匹配的项目，然后
• MINUS设置运算符以查找哪些匹配项目没有匹配年份

---

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>

然而，还有另一种方法可以使用；

到外部(left(按相应的年份列连接表，然后按第二个(如(的null值年份列进行筛选

SELECT p.* 
FROM projects p
LEFT JOIN project_finances pf
ON pf.year_funding = p.year_construction
WHERE pf.year_funding IS NULL

演示