如何通过SQL查询实现基于NAME分组的LOCATION条件过滤

我是SQL新手，在过滤数据表时遇到困难。原数据表如下：

CATEGORY     |      NAME         |     UID         |  LOCATION
------------------------------------------------------------------------
Planning     |      Test007      | AVnNDZEGp5JaMD  |  USER
Planning     |      Test007      | AVjNDZEGp5JaMD  |  SITE
Planning     |      Test007      | NULL            |  NULL
Develop      |      Test008      | AZkNDZEGp5JaMD  |  USER
Develop      |      Test008      | NULL            |  NULL
Workspace    |      Test10       | QWrNjwaEp5JaMD  |  USER
Workspace    |      Test10       | NULL            |  NULL
Workspace    |      Test10       | NULL            |  SITE

对表中每个唯一的NAME：

• 若该NAME存在LOCATION = 'SITE'的行，则排除该NAME下所有LOCATION = NULL的行
• 若该NAME不存在LOCATION = 'SITE'的行，则保留所有行（包括LOCATION = NULL的行）

示例：Test007存在LOCATION='SITE'的行，因此需要排除其LOCATION=NULL的行。

这里提供两种通用的SQL写法，适用于大多数主流数据库（如MySQL、PostgreSQL、SQL Server等）。

方法1：窗口函数（推荐） #

通过窗口函数标记每个NAME是否包含LOCATION='SITE'的行，再进行过滤：

SELECT 
    CATEGORY, NAME, UID, LOCATION
FROM (
    SELECT 
        *,
        -- 标记当前NAME是否存在LOCATION='SITE'的行，1表示存在，0表示不存在
        MAX(CASE WHEN LOCATION = 'SITE' THEN 1 ELSE 0 END) OVER (PARTITION BY NAME) AS has_site
    FROM your_table_name -- 替换为你的实际表名
) t
WHERE 
    -- 没有SITE行的NAME，保留所有记录
    has_site = 0 
    -- 有SITE行的NAME，只保留LOCATION不为NULL的记录
    OR (has_site = 1 AND LOCATION IS NOT NULL);

方法2：关联子查询 #

通过NOT EXISTS判断当前NAME是否存在LOCATION='SITE'的行，再构建过滤条件：

SELECT 
    CATEGORY, NAME, UID, LOCATION
FROM your_table_name t -- 替换为你的实际表名
WHERE 
    -- 保留LOCATION不为NULL的记录
    LOCATION IS NOT NULL
    -- 或者当前NAME不存在SITE行时，保留LOCATION为NULL的记录
    OR NOT EXISTS (
        SELECT 1 
        FROM your_table_name 
        WHERE NAME = t.NAME AND LOCATION = 'SITE'
    );

执行上述任意一种查询后，将得到如下结果：

CATEGORY     |      NAME         |     UID         |  LOCATION
------------------------------------------------------------------------
Planning     |      Test007      | AVnNDZEGp5JaMD  |  USER
Planning     |      Test007      | AVjNDZEGp5JaMD  |  SITE
Develop      |      Test008      | AZkNDZEGp5JaMD  |  USER
Develop      |      Test008      | NULL            |  NULL
Workspace    |      Test10       | QWrNjwaEp5JaMD  |  USER
Workspace    |      Test10       | NULL            |  SITE

Test007和Test10中LOCATION为NULL的条目已被排除。

内容的提问来源于stack exchange，提问作者YASH JADHAV