如何使用row_number和partition by找到重复值

Question

如何使用row_number和partition by找到重复值

3

我想知道如何使用以下查询获取所有重复值。请告诉我查询有什么问题。

问候， Iftekhar

SQL> desc tmp_emp_Area
 Name                            Null?    Type
 ------------------------------- -------- ----
 SC_CD                                    VARCHAR2(2)
 DIST_CD                                  VARCHAR2(2)
 THA_CD                                   VARCHAR2(2)
 UN_CD                                    VARCHAR2(3)
 FP_ID                                    VARCHAR2(4)
 S_DT                                     DATE
 END_DT                                   DATE
 PERFORM                                  VARCHAR2(1)
 BS_CD                                    VARCHAR2(4)
 MKT_CD                                   VARCHAR2(3)

查询：

  SELECT SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,
    row_number() over(partition BY SC_CD, DIST_CD, THA_CD, UN_CD, FP_ID order by FP_ID) rn
    FROM tmp_emp_area
    WHERE rn >1

SQL> / WHERE rn >1 * 第4行出现错误：ORA-00904: "RN": 无效标识符

请查看下面这两个查询结果，它们分别用于获取重复项，一个显示92列，另一个显示96列。

select SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,count(fp_id)  
from tmp_emp_area 
group by SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID 
having count(fp_id)>1
/

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 02 022 5J85            2
14 61 02 098 5J85            3
14 64 02 004 5J85            2
14 64 02 002 5J85            2
14 60 19 060 5F77            2
14 60 13 077 5F77            2
14 61 06 006 5D51            2
14 61 07 013 5D51            2
14 61 07 083 5D51            2
14 61 06 010 5D51            2
14 61 01 015 5R44            2
14 61 08 027 5R44            2
14 61 01 057 5R44            2
14 61 01 067 5R44            2
14 61 05 001 5R44            2
14 61 05 003 5R44            2
14 61 02 009 5J85            2
14 60 13 078 5F77            2
14 61 06 007 5D51            2
14 61 01 021 5R44            2
14 61 01 029 5R44            2

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 01 065 5R44            2
14 61 01 069 5R44            2
14 64 03 013 5J85            2
14 61 02 014 5J85            2
14 61 02 089 5J85            2
14 60 19 132 5F77            2
14 60 19 134 5F77            2
14 61 07 086 5D51            2
14 61 06 035 5D51            2
14 61 06 014 5D51            2
14 61 01 031 5R44            2
14 61 01 036 5R44            2
14 61 01 041 5R44            2
14 61 02 092 5J85            3
14 61 02 074 5J85            3
14 61 02 088 5J85            2
14 61 02 109 5J85            2
14 60 19 014 5F77            2
14 61 07 015 5D51            2
14 61 06 008 5D51            2
14 61 06 016 5D51            2

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 05 047 5R44            2
14 61 01 018 5R44            2
14 61 01 055 5R44            2
14 61 01 066 5R44            2
14 61 01 024 5R44            2
14 61 02 093 5J85            3
14 64 02 011 5J85            2
14 64 02 003 5J85            2
14 61 09 002 5J85            2
14 61 02 081 5J85            2
14 61 05 053 5D51            2
14 61 07 087 5D51            2
14 61 06 036 5D51            2
14 61 06 020 5D51            2
14 61 01 076 5R44            2
14 61 02 059 5R44            2
14 61 02 033 5J85            2
14 64 02 008 5J85            2
14 64 02 020 5J85            2
14 61 02 097 5J85            2
14 61 02 017 5J85            2

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 02 082 5J85            2
14 61 01 077 5R44            2
14 61 05 046 5R44            2
14 61 01 017 5R44            2
14 61 01 054 5R44            2
14 64 02 030 5J85            2
14 61 02 010 5J85            2
14 61 02 103 5J85            2
14 64 02 006 5J85            2
14 64 03 020 5J85            2
14 61 02 105 5J85            2
14 61 02 080 5J85            2
14 61 02 151 5J85            2
14 60 19 059 5F77            2
14 61 06 045 5D51            2
14 61 01 075 5R44            2
14 61 01 056 5R44            2
14 61 01 020 5R44            2
14 61 05 007 5R44            2
14 61 01 053 5R44            2
14 61 01 078 5R44            2

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 02 013 5J85            2
14 64 02 010 5J85            2
14 64 02 001 5J85            2
14 61 02 077 5J85            2
14 61 07 033 5D51            2
14 61 01 033 5R44            2
14 61 01 068 5R44            2
14 61 01 073 5R44            2

92 rows selected.



  select *
 from
  (
    SELECT SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,
       row_number() over(partition BY SC_CD, DIST_CD, THA_CD, UN_CD, FP_ID order by FP_ID) rn
    FROM tmp_emp_area
  ) dt
 WHERE rn >1
SQL> /


SC DI TH UN_ FP_I        RN
-- -- -- --- ---- ---------
14 60 13 077 5F77         2
14 60 13 078 5F77         2
14 60 19 014 5F77         2
14 60 19 059 5F77         2
14 60 19 060 5F77         2
14 60 19 132 5F77         2
14 60 19 134 5F77         2
14 61 01 015 5R44         2
14 61 01 017 5R44         2
14 61 01 018 5R44         2
14 61 01 020 5R44         2
14 61 01 021 5R44         2
14 61 01 024 5R44         2
14 61 01 029 5R44         2
14 61 01 031 5R44         2
14 61 01 033 5R44         2
14 61 01 036 5R44         2
14 61 01 041 5R44         2
14 61 01 053 5R44         2
14 61 01 054 5R44         2
14 61 01 055 5R44         2

SC DI TH UN_ FP_I        RN
-- -- -- --- ---- ---------
14 61 01 056 5R44         2
14 61 01 057 5R44         2
14 61 01 065 5R44         2
14 61 01 066 5R44         2
14 61 01 067 5R44         2
14 61 01 068 5R44         2
14 61 01 069 5R44         2
14 61 01 073 5R44         2
14 61 01 075 5R44         2
14 61 01 076 5R44         2
14 61 01 077 5R44         2
14 61 01 078 5R44         2
14 61 02 009 5J85         2
14 61 02 010 5J85         2
14 61 02 013 5J85         2
14 61 02 014 5J85         2
14 61 02 017 5J85         2
14 61 02 022 5J85         2
14 61 02 033 5J85         2
14 61 02 059 5R44         2
14 61 02 074 5J85         2

SC DI TH UN_ FP_I        RN
-- -- -- --- ---- ---------
14 61 02 074 5J85         3
14 61 02 077 5J85         2
14 61 02 080 5J85         2
14 61 02 081 5J85         2
14 61 02 082 5J85         2
14 61 02 088 5J85         2
14 61 02 089 5J85         2
14 61 02 092 5J85         2
14 61 02 092 5J85         3
14 61 02 093 5J85         2
14 61 02 093 5J85         3
14 61 02 097 5J85         2
14 61 02 098 5J85         2
14 61 02 098 5J85         3
14 61 02 103 5J85         2
14 61 02 105 5J85         2
14 61 02 109 5J85         2
14 61 02 151 5J85         2
14 61 05 001 5R44         2
14 61 05 003 5R44         2
14 61 05 007 5R44         2

SC DI TH UN_ FP_I        RN
-- -- -- --- ---- ---------
14 61 05 046 5R44         2
14 61 05 047 5R44         2
14 61 05 053 5D51         2
14 61 06 006 5D51         2
14 61 06 007 5D51         2
14 61 06 008 5D51         2
14 61 06 010 5D51         2
14 61 06 014 5D51         2
14 61 06 016 5D51         2
14 61 06 020 5D51         2
14 61 06 035 5D51         2
14 61 06 036 5D51         2
14 61 06 045 5D51         2
14 61 07 013 5D51         2
14 61 07 015 5D51         2
14 61 07 033 5D51         2
14 61 07 083 5D51         2
14 61 07 086 5D51         2
14 61 07 087 5D51         2
14 61 08 027 5R44         2
14 61 09 002 5J85         2

SC DI TH UN_ FP_I        RN
-- -- -- --- ---- ---------
14 64 02 001 5J85         2
14 64 02 002 5J85         2
14 64 02 003 5J85         2
14 64 02 004 5J85         2
14 64 02 006 5J85         2
14 64 02 008 5J85         2
14 64 02 010 5J85         2
14 64 02 011 5J85         2
14 64 02 020 5J85         2
14 64 02 030 5J85         2
14 64 03 013 5J85         2
14 64 03 020 5J85         2

96 rows selected.

- Iftekhar

你不能使用GROUP BY吗？我认为这样会更容易。 - pablomatico

@pablomatico，我已经修改了查询语句并得到了结果，请查看。两个不同的查询语句有不同的结果。 - Iftekhar

2

在第二个查询中（带有partition by的那个），您选择了每一行row_number > 1。这意味着在一个有3行的组中，您选择了其中的2行（即第2行和第3行）。而在第一个查询中（带有group by的那个），同一组将只产生一行，其count(fp_id) = 3。这就是为什么您的结果中会得到不同数量的行的原因。 - pablomatico

@pablomatico，谢谢你的解释。 - Iftekhar

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- dnoeth · Accepted Answer

你不能在WHERE语句中使用别名，请改用派生表：

select *
from
 (
   SELECT SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,
      row_number() over(partition BY SC_CD, DIST_CD, THA_CD, UN_CD, FP_ID order by FP_ID) rn
   FROM tmp_emp_area
 ) dt
WHERE rn >1