基于行值筛选记录的SQL实现需求及问题求助

API 探路者

2026-8-10

问题：按指定规则筛选test1表记录

需要根据行值筛选test1表中的记录，规则如下：

若某一ESAProjectID对应的projecttype列中不存在Execution Project，则选取projecttype为Group Project的行；
若同一ESAProjectID同时存在Execution Project和Group Project两种类型，则仅选取projecttype为Execution Project的行。

用户尝试了以下SQL但未达到预期效果：

SELECT DISTINCT a.ESAProjectID, a.projecttype
  FROM test1 a
 INNER JOIN test1 b
    ON a.ESAProjectID = b.ESAProjectID
 WHERE a.projecttype = 'Group Project'

解决方案

方法1：窗口函数实现（逻辑直观）

先给每个ESAProjectID分组，标记该组是否存在Execution Project，再根据标记筛选符合规则的行：

WITH project_group_stats AS (
    SELECT 
        ESAProjectID,
        projecttype,
        -- 标记当前分组是否有Execution Project
        MAX(CASE WHEN projecttype = 'Execution Project' THEN 1 ELSE 0 END) OVER (PARTITION BY ESAProjectID) AS has_execution_project
    FROM test1
)
SELECT ESAProjectID, projecttype
FROM project_group_stats
WHERE 
    (has_execution_project = 1 AND projecttype = 'Execution Project')
    OR (has_execution_project = 0 AND projecttype = 'Group Project');

方法2：NOT EXISTS子查询实现

通过子查询判断当前ESAProjectID下是否存在Execution Project，直接过滤出符合条件的行：

SELECT ESAProjectID, projecttype
FROM test1 t_main
WHERE 
    -- 优先保留Execution Project类型的行
    projecttype = 'Execution Project'
    -- 当该分组没有Execution Project时，保留Group Project类型
    OR (
        projecttype = 'Group Project'
        AND NOT EXISTS (
            SELECT 1 
            FROM test1 t_sub 
            WHERE t_sub.ESAProjectID = t_main.ESAProjectID 
              AND t_sub.projecttype = 'Execution Project'
        )
    );