删除条件重复值

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的附加列代码跨所有行。外部查询然后对这些进行过滤，以便SR = 1的行只在该代码的最小值也为1时才被保留;即SR = 0时该代码不存在行。

有了您的虚拟数据，让：使用CTE与您的数据，并显示以及在子查询额外的分析，分列

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

SQL Fiddle demo。

您也可以将其简化为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更具可读性分普尔的答案，但。但它确实避免了分析功能。