Oracle中用PL/SQL筛选不含全零列行的Item记录方案

需求说明 #

需要筛选出不存在任何一行Col1和Col2同时为0的Item，返回这些Item的所有行（仅保留Item和Attr列）。

原表数据 #

假设表名为your_table，数据如下：

实现方案 #

以下是几种高效的SQL实现方式（PL/SQL中可直接使用这些SQL语句，也可封装到存储过程中）：

方法1：使用NOT EXISTS子查询

SELECT t.Item, t.Attr
FROM your_table t
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.Item = t.Item
      AND t2.Col1 = 0
      AND t2.Col2 = 0
);

逻辑：逐行检查当前Item是否存在Col1和Col2同时为0的行，若不存在则保留该行。

方法2：使用窗口函数统计无效行数

SELECT Item, Attr
FROM (
    SELECT 
        Item, 
        Attr,
        -- 统计当前Item下Col1和Col2同时为0的行数
        COUNT(CASE WHEN Col1 = 0 AND Col2 = 0 THEN 1 END) OVER (PARTITION BY Item) AS zero_row_count
    FROM your_table
) sub_query
WHERE zero_row_count = 0;

逻辑：通过窗口函数按Item分组统计无效行数，筛选出无效行数为0的行。

方法3：先筛选有效Item再关联原表

SELECT t.Item, t.Attr
FROM your_table t
INNER JOIN (
    SELECT Item
    FROM your_table
    GROUP BY Item
    -- 统计无效行数，筛选出无效行数为0的Item
    HAVING SUM(CASE WHEN Col1 = 0 AND Col2 = 0 THEN 1 ELSE 0 END) = 0
) valid_items ON t.Item = valid_items.Item;

逻辑：先通过分组筛选出符合条件的Item列表，再关联原表获取这些Item的所有数据。

期望结果 #

执行上述任意语句后，将得到如下结果：

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