如何基于指定条件从students表中筛选目标记录

表结构与测试数据 #

CREATE TABLE students 
(
    id INTEGER PRIMARY KEY,
    Name  TEXT NOT NULL,
    Version Text,
    Topic TEXT NOT NULL,
    Department TEXT NOT NULL
);

-- 插入测试数据
INSERT INTO students VALUES (1, 'Asset A','1' ,'Song','Song');
INSERT INTO students VALUES (2, 'Asset B','1','IndustryX', 'IndustryX');
INSERT INTO students VALUES (3, 'Asset C','1','Strategy', 'IndustryX');
INSERT INTO students VALUES (4, 'Asset D','1','Strategy', 'IndustryX');
INSERT INTO students VALUES (5, 'Asset D','1','Cross Function', 'IndustryX');
INSERT INTO students VALUES (6, 'Asset E','1','Cross Function', 'IndustryX');
INSERT INTO students VALUES (7, 'Asset F','1','Cross Function', 'Song');
INSERT INTO students VALUES (8, 'Asset G','1','Cross Function', 'Song');
INSERT INTO students VALUES (9, 'Asset G','1','Strategy', 'Song');
INSERT INTO students VALUES (10, 'Asset H','1','Strategy', 'Song');
INSERT INTO students VALUES (11, 'Asset A','2' ,'Strategy','Song');
INSERT INTO students VALUES (12, 'Asset A','1' ,'Cross Function','Song');

当前全量查询结果 #

筛选规则 #

• 当Department为Song时，若该Version下存在Topic为Song或Cross Function的记录，该行不返回
• 当Department为IndustryX时，若该Version下存在Topic为IndustryX或Cross Function的记录，该行不返回

预期结果 #

实现方案 #

方法1：使用EXISTS子查询 #

通过子查询检查同部门同版本下是否存在需要排除的Topic，只保留不存在这些Topic的记录：

SELECT s.*
FROM students s
WHERE NOT EXISTS (
    SELECT 1
    FROM students s2
    WHERE s2.Department = s.Department
      AND s2.Version = s.Version
      AND (
          (s.Department = 'Song' AND s2.Topic IN ('Song', 'Cross Function'))
          OR (s.Department = 'IndustryX' AND s2.Topic IN ('IndustryX', 'Cross Function'))
      )
);

方法2：使用窗口函数 #

先通过窗口函数标记每个(Department, Version)分组是否需要排除，再筛选出无需排除的记录：

SELECT id, Name, Version, Topic, Department
FROM (
    SELECT *,
           CASE 
               WHEN Department = 'Song' 
                    AND COUNT(CASE WHEN Topic IN ('Song', 'Cross Function') THEN 1 END) OVER (PARTITION BY Department, Version) > 0 
               THEN 1
               WHEN Department = 'IndustryX' 
                    AND COUNT(CASE WHEN Topic IN ('IndustryX', 'Cross Function') THEN 1 END) OVER (PARTITION BY Department, Version) > 0 
               THEN 1
               ELSE 0
           END AS exclude_flag
    FROM students
) t
WHERE exclude_flag = 0;

两种方法都能准确筛选出符合要求的记录，可根据实际数据库环境选择使用。

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