Oracle Sql Delete with row number抛出错误ORA-00904："RN"：标识符无效

Question

我试图使用select & row number从一个表中删除，但它在最后一行抛出了错误。

delete from ZZ_temp_Value where (nach_id,vv_lfd,language,value,dq_nr,emptyvalue,unit) IN 
(select  t.*,ROW_NUMBER() OVER (PARTITION BY vv_lfd ORDER BY emptyvalue,position) rn 
     from 
       (select distinct w.*, q.position 
        from ZZ_temp_Value w, zz_def d, dv_format q 
         on q.nach_id = w.nach_id
        and q.vv_lfd = w.vv_lfd
      order by  w.emptyvalue, q.position)t
       where t.rn < 1 ;

或者我想从行号不是1的表ZZ_temp_Value中删除select中的值。

select  t.*,ROW_NUMBER() OVER (PARTITION BY vv_lfd ORDER BY emptyvalue,position) rn 
         from 
           (select distinct w.*, q.position 
            from ZZ_temp_Value w, zz_def d, dv_format q 
             on q.nach_id = w.nach_id
            and q.vv_lfd = w.vv_lfd
          order by  w.emptyvalue, q.position

Answer 1

在您尝试的查询中，有几个语法和逻辑问题：

• ON子句：使用没有匹配JOIN的ON子句。如果将ON替换为WHERE (在SQL中称为implicit join.

的旧的、不推荐使用的连接表的方法)，您的查询可能会正常工作

• Cross Join：就像隐式连接的缺点一样，您错误地(或有意地？)未将zz_def d与匹配的列联接。当您匹配q和w时，如果不匹配d，则会在该表上呈现交叉联接，以返回比预期更多的行。此外，SELECT.

中也不使用d

• Misaligned子查询：分层的子查询显示缺少右括号，其中未对齐引用不同级别的计算列rn的表别名t。

• WHERE逻辑：由于ROW_NUMBER返回正值，因此一旦解决语法错误，您的逻辑条件t.RN < 1可能不会返回任何结果。

此外，考虑一些提示：

• Explicit Join：使用显式JOIN来连接表，避免运行交叉连接的细微错误。

• SELECT *：避免使用SELECT *来更好地控制显示，并直观地显示选定的列。具体地说，子查询中没有要与外部查询匹配的以下列：nach_id, language, value, dq_nr。

• Column别名：给SELECT中的所有列加上别名，以直观地向表显示源代码。具体地说，您的窗口函数没有显式引用列的表源。理想情况下，您应该运行PARTITION BY w.vv_lfd ORDER BY w.emptyvalue, q.position.

• Table别名：使用比所选字母更好的表别名，以避免代码阅读器扫描回FROM和JOIN子句以查找其原始源。理想情况下，

来自ZZ_temp_Value t ..。zz_def d ..来自ZZ_temp_Value tmp的dv_format f ...zz_def定义...dv_format fmt

假设您最终需要删除行数真正大于1的行(rn > 1)，请考虑调整后的查询(需要测试)。

DELETE FROM ZZ_temp_Value 
WHERE (nach_id, vv_lfd, language, value, dq_nr, emptyvalue, unit) IN 
  (SELECT t.nachi, t.vvlfd, t.language, t.value, t.dq_nr, t.emptyvalue, t.unit 
   FROM 
     (SELECT tmp.nachi, tmp.vv_lfd, tmp.language, tmp.value
             , tmp.dq_nr, tmp.emptyvalue, tmp.unit 
             , ROW_NUMBER() OVER (PARTITION BY tmp.vv_lfd 
                                  ORDER BY tmp.emptyvalue, fmt.position) rn
      FROM ZZ_temp_Value tmp
      INNER JOIN dv_format fmt
          ON  fmt.vv_lfd = tmp.vv_lfd
          AND fmt.nach_id = tmp.nach_id
      -- CROSS JOIN zz_def d                     -- CAREFULLY THINK ABOUT THIS! 
     ) t
   WHERE t.rn > 1
  );

DISTINCT和ORDER BY已被删除为冗余。对于重复的记录，ROW_NUMBER()将重复，而对于外部查询结果，将忽略行的子查询排序。