基于行比较逻辑使用集合操作查询非唯一字段表记录的需求咨询

嘿，这种场景确实挺常见的——有时候业务逻辑就是需要根据记录的分组数量来做不同的筛选，咱们来搞定它！

首先先明确你的表和数据：
你的表是 test.dbo.test_tsac，字段为 Id, PType, TNo，插入的数据如下：

insert into test.dbo.test_tsac values 
(1, 'tf' , 2 ), 
(2, 'pg' , 3 ), 
(3, 'tf' , 1 ), 
(3, 'pg' , 1 ), 
(4, 'tf' , 5 ), 
(4, 'LL' , 2 ), 
(4, 'pg' , 1 ), 
(5, 'pg' , 2 ), 
(5, 'pg' , 4 );

你已经明确了第一条需求：如果某个Id只对应一条记录，直接把这条记录放到结果集里（比如示例里的(1, 'tf' , 2 )和(2, 'pg' , 3 )）。不过看起来第二条需求没写完，我先假设几个常见的业务场景对应的解决方案，你可以根据实际需求调整：

场景1：多记录的Id，仅保留PType为pg的记录 #

如果你的需求是“有多条记录的Id，只留其中PType是pg的行；如果该Id没有pg类型的记录，就保留所有行”，可以用下面的SQL：

WITH IdStats AS (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY Id) AS total_records,
        EXISTS (SELECT 1 FROM test.dbo.test_tsac t WHERE t.Id = test_tsac.Id AND t.PType = 'pg') AS has_pg
    FROM test.dbo.test_tsac
)
SELECT Id, PType, TNo
FROM IdStats
WHERE 
    total_records = 1
    OR (total_records > 1 AND (has_pg = 0 OR PType = 'pg'));

场景2：多记录的Id，任意保留一条记录 #

如果需求是“有多条记录的Id，随便留一条就行”，可以用窗口函数ROW_NUMBER()来实现：

WITH RankedRecords AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY (SELECT NULL)) AS rn
    FROM test.dbo.test_tsac
)
SELECT Id, PType, TNo
FROM RankedRecords
WHERE rn = 1;

这里ORDER BY (SELECT NULL)是让数据库随机选一条，如果你有特定的排序规则（比如按TNo升序），把它替换成ORDER BY TNo ASC即可。

场景3：多记录的Id，仅保留所有记录TNo相同的组 #

如果需求是“当某个Id的所有记录TNo都一样时保留该组，否则丢弃”，可以这样写：

WITH IdTNoStats AS (
    SELECT 
        *,
        COUNT(DISTINCT TNo) OVER (PARTITION BY Id) AS distinct_tno_count
    FROM test.dbo.test_tsac
)
SELECT Id, PType, TNo
FROM IdTNoStats
WHERE 
    distinct_tno_count = 1;

这个结果会保留Id=1、2、3的所有记录，因为它们的TNo要么只有一个，要么全部相同。

如果你的实际需求和上面的场景都不一样，补充一下第二条需求的具体规则，我再帮你调整代码~

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