删除条件重复值

Question

我在查询中使用了16个表，并试图通过应用以下新的更改来优化查询。因此，我们不能使用group或union子句，因为由于性能问题，无法在现有查询中应用。

我正在寻找分区的排名/计数/行号意味着行级解决方案，如果可能的话。

select * FROM (
SELECT 'HA' as CODE,0 AS SR FROM DUAL UNION ALL
SELECT 'HA' as CODE,0 AS SR FROM DUAL UNION ALL
SELECT 'OF' as CODE,0 AS SR FROM DUAL UNION ALL
SELECT 'AC' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'OF' as CODE,0 AS SR FROM DUAL UNION ALL
SELECT 'OF' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'OF' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'XY' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'XY' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'HA' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'OF' as CODE,1 AS SR FROM DUAL )
ORDER BY SR

对于上面的查询示例数据，我希望：

1. 将所有行保存在SR=0的位置
2. 删除代码值存在于( SR=1 )中的SR=0行

例如，包含SR=1的"AC“行是因为在SR=0中没有"AC”行，而删除带有SR=1的"HA“行是因为有一个带有SR=0的"HA”行。

这些数据所需的输出：

HA   0
OF   0
HA   0
OF   0
XY   1
XY   1
AC   1

Answer 1

您可以添加子查询和分析min()。

select code, sr
from (
  select code, sr, min(sr) over (partition by code) as min_sr
  from (
    <your query>
  )
)
where sr = 0
or (sr = 1 and min_sr = 1)
order by sr;

对于每一行，子查询将为所有行的代码获得一个额外的列，该列的SR值最低。然后，外部查询对这些行进行筛选，以便只有当该代码的最小值也为1时，才保留带有SR=1的行；也就是说，该代码在SR=0中没有行。

使用您的虚拟数据提供：

CODE         SR
---- ----------
OF            0 
OF            0 
HA            0 
HA            0 
XY            1 
XY            1 
AC            1 

演示对数据使用CTE，并在子查询中显示额外的分析-min列。

您还可以将其简化为where sr = min_sr，但将其分为两个子句可能会使逻辑和规则更加清晰。

Answer 2

您还可以尝试以下方法，将上面的子查询放到WITH中

WITH s AS (
    SELECT 'HA' as CODE,0 AS SR FROM DUAL UNION ALL
    SELECT 'HA' as CODE,0 AS SR FROM DUAL UNION ALL
    SELECT 'OF' as CODE,0 AS SR FROM DUAL UNION ALL
    SELECT 'AC' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'OF' as CODE,0 AS SR FROM DUAL UNION ALL
    SELECT 'OF' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'OF' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'XY' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'XY' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'HA' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'OF' as CODE,1 AS SR FROM DUAL )
SELECT code, sr FROM s s1 -- 's1' is the table alias
 WHERE s1.sr = 0
    OR ( s1.sr = 1
     AND NOT EXISTS ( SELECT 1 FROM s s2 -- 's2' is the table alias
                       WHERE s2.code = s1.code
                         AND s2.sr = 0 ) )

结果：

HA  0
HA  0
OF  0
OF  0
AC  1
XY  1
XY  1

不过，对于我是否认为这比@Alex的答案更具可读性，我意见不一。但它确实避免了解析函数。