Oracle SQL查询：相同Client ID、税年及生效日但不同税码与薪资ID的记录

方法1：筛选分组内所有记录的tax_code与Payroll_id均互不重复的记录 #

适用于需求为「同一client_id、Tax_year、Tax_effective_date分组下，任意两条记录的tax_code和Payroll_id都不相同」的场景：

WITH grouped_data AS (
    SELECT 
        t.*,
        COUNT(DISTINCT tax_code) OVER (PARTITION BY client_id, Tax_year, Tax_effective_date) AS distinct_tax_count,
        COUNT(DISTINCT Payroll_id) OVER (PARTITION BY client_id, Tax_year, Tax_effective_date) AS distinct_payroll_count,
        COUNT(*) OVER (PARTITION BY client_id, Tax_year, Tax_effective_date) AS total_records
    FROM your_table_name t
)
SELECT 
    client_id,
    Tax_year,
    Tax_effective_date,
    tax_code,
    Payroll_id,
    -- 替换为你需要保留的其他列名
    other_column_1,
    other_column_2
FROM grouped_data
WHERE distinct_tax_count = total_records 
  AND distinct_payroll_count = total_records;

方法2：筛选存在至少一条同分组记录与自身tax_code、Payroll_id均不同的记录 #

适用于需求为「只要同分组内存在另一条记录和当前记录的tax_code、Payroll_id都不一样，就保留当前记录」的场景：

SELECT DISTINCT t1.*
FROM your_table_name t1
JOIN your_table_name t2 
    ON t1.client_id = t2.client_id
    AND t1.Tax_year = t2.Tax_year
    AND t1.Tax_effective_date = t2.Tax_effective_date
    AND t1.tax_code != t2.tax_code
    AND t1.Payroll_id != t2.Payroll_id;

注意事项 #

• 将代码中的your_table_name替换为你的实际数据表名称；
• 方法1中的other_column_1, other_column_2替换为需要显示的其他列名。

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