如何在SQL中对表进行二次筛选？附业务场景实现方案

现有数据表 #

COMPANY（全量公司信息表） #

RECORDS（全量公司记录表） #

关联规则：各表的ID为唯一标识，COMPANY.ID与RECORDS.Company_id为关联字段。

查询需求 #

1. 先筛选出**记录日期 = '02.02.2024'**的记录，提取对应的Company_id
2. 针对这些Company_id，筛选出对应公司的两类有效记录：
   • 记录日期为'02.02.2024'且归档状态为0的记录
   • 记录日期早于2020年且归档状态为0的记录
3. 输出符合条件的完整数据集，并统计每个公司的有效记录总数

预期结果 #

符合条件的数据集 #

各公司有效记录统计 #

SQL实现方案 #

方案：使用CTE拆分查询逻辑 #

-- 获取有2024-02-02记录的目标公司ID
WITH target_companies AS (
    SELECT DISTINCT Company_id
    FROM RECORDS
    WHERE 记录日期 = '02.02.2024'
)
-- 筛选目标公司的有效记录
SELECT r.*
FROM RECORDS r
JOIN target_companies tc ON r.Company_id = tc.Company_id
WHERE (r.记录日期 = '02.02.2024' AND r.归档状态 = 0)
   OR (STR_TO_DATE(r.记录日期, '%d.%m.%Y') < '2020-01-01' AND r.归档状态 = 0)
ORDER BY r.Company_id, r.ID;

-- 统计各公司有效记录数
WITH target_companies AS (
    SELECT DISTINCT Company_id
    FROM RECORDS
    WHERE 记录日期 = '02.02.2024'
),
valid_records AS (
    SELECT r.Company_id
    FROM RECORDS r
    JOIN target_companies tc ON r.Company_id = tc.Company_id
    WHERE (r.记录日期 = '02.02.2024' AND r.归档状态 = 0)
       OR (STR_TO_DATE(r.记录日期, '%d.%m.%Y') < '2020-01-01' AND r.归档状态 = 0)
)
SELECT Company_id, COUNT(*) AS 记录总数
FROM valid_records
GROUP BY Company_id;

说明 #

• 使用STR_TO_DATE将字符串日期转换为日期类型，确保日期比较的准确性（不同数据库函数有差异：SQL Server用CONVERT，Oracle用TO_DATE）
• 通过CTE拆分逻辑，让查询结构更清晰、易维护

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