使用ROW_NUMBER()与PARTITION BY后如何仅保留行数大于1的全部分区数据
解决方案
核心逻辑是新增窗口函数统计每个ID分区的总记录数,过滤总记录数大于1的分区即可,不会影响原有ROW_NUMBER的计算结果,也能完整保留符合要求分区内的所有行(包括ROWNUM=1的记录)。
你可以直接在原有查询基础上修改,示例代码如下:
WITH partitioned_data AS ( SELECT VALUE, ID, -- 此处保留你原有的ROW_NUMBER逻辑,ORDER BY后的内容替换为你原来的排序规则即可 ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [你原来的排序字段]) AS ROWNUM, -- 新增统计每个ID分区的总记录数 COUNT(*) OVER (PARTITION BY ID) AS partition_total FROM 你的原表名 ) SELECT VALUE, ID, ROWNUM FROM partitioned_data WHERE partition_total > 1;
备选方案(分组子查询过滤)
如果你不想使用额外的窗口函数,也可以先查询出所有符合条件的ID列表,再关联过滤:
-- 先筛选出记录数大于1的ID WITH valid_ids AS ( SELECT ID FROM 你的原表名 GROUP BY ID HAVING COUNT(*) > 1 ) SELECT t.VALUE, t.ID, t.ROWNUM FROM ( SELECT VALUE, ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [你原来的排序字段]) AS ROWNUM FROM 你的原表名 ) t INNER JOIN valid_ids v ON t.ID = v.ID;
两种方案均可以实现需求,第一种窗口函数方案仅需一次表扫描,在大多数数据库中的执行效率更高,更适合数据量较大的场景。
内容的提问来源于stack exchange,提问作者dhwu
相关产品推荐
相关产品推荐

