SQL Server如何基于列值控制规则生效，按发票日期要求输出查询结果

我对实现如下输出效果的查询语句存在疑问，具体规则如下：

1. 若发票日期（Invoice Date）不存在于示例数据库中，且同一发票编号（INVOICE NUMBER）对应2个过账日期（Posting date），仅保留最早过账日期的单行记录，例如示例中的中国（China）行；
2. 若发票日期存在于示例数据库中，且同一发票编号对应2个过账日期，保留该发票编号下所有原行记录不变，例如示例中的伦敦（London）、澳大利亚（Australia）行；
3. 其余仅对应单个过账日期的行，无论发票日期是否存在于数据库中，均保留原行记录，例如示例中的印度（India）行。

现有示例表数据 #

期望输出效果 #

备注：上述为示例表，实际场景下将由代码库动态填充数值和列，并非要固定过滤掉美国行，而是需要符合上述逻辑的通用查询方案。

假设存储合法发票日期的基准表名为valid_invoice_dates，对应日期字段为invoice_date，业务表名为invoice_data，可使用窗口函数实现单SELECT查询：

WITH invoice_ranked AS (
    SELECT
        *,
        -- 统计每个发票编号对应的过账日期行数
        COUNT(*) OVER (PARTITION BY "INVOICE NUMBER") AS post_cnt,
        -- 给同一发票编号的行按过账日期升序排序，最早的排第1
        ROW_NUMBER() OVER (PARTITION BY "INVOICE NUMBER" ORDER BY TO_DATE("Posting date", 'DD.MM.YYYY') ASC) AS post_rn,
        -- 判断当前行的发票日期是否存在于合法日期表
        EXISTS (SELECT 1 FROM valid_invoice_dates vid WHERE vid.invoice_date = TO_DATE("Invoice Date", 'DD.MM.YYYY')) AS date_valid
    FROM invoice_data
)
SELECT "INVOICE NUMBER", "NAME", "Invoice Date", "Posting date", "Place"
FROM invoice_ranked
WHERE
    -- 规则2：日期合法且有2个过账日期的全部保留
    (date_valid = TRUE AND post_cnt = 2)
    -- 规则1：日期不合法且有2个过账日期的仅保留排序第一的最早日期行
    OR (date_valid = FALSE AND post_cnt = 2 AND post_rn = 1)
    -- 规则3：只有1个过账日期的全部保留
    OR post_cnt = 1;

逻辑说明 #

1. 先用CTE给每一行追加三个辅助计算字段，避免多层嵌套查询
2. 日期转换函数TO_DATE可根据你使用的数据库类型调整（比如MySQL用STR_TO_DATE，SQL Server用CONVERT），确保日期排序逻辑正确
3. 若你所说的「发票日期存在于数据库」是指该日期在当前业务表的所有行中存在，仅需把EXISTS子句替换为：

EXISTS (SELECT 1 FROM invoice_data id WHERE TO_DATE(id."Invoice Date", 'DD.MM.YYYY') = TO_DATE(invoice_data."Invoice Date", 'DD.MM.YYYY')) AS date_valid

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