代码之家 › 专栏 › 技术社区 › Error_2646

按组检查多个不同的事件

oracle11g oracle sql

Error_2646 · 技术社区 · 6 年前

输入数据

   Some_Table
   ST_Field1   ST_Field2
   Apple       A
   Apple       A
   Apple       D
   Orange      D
   Orange      E
   Orange      Z
   Pear        D
   Pear        G 
   Pear        C

   Reference_Table
   RT_Field1   RT_Field2
   1           A
   1           B
   1           C
   2           D
   2           E
   2           F
   3           G

   ST_Field1   ST_Field2
   Orange      D
   Orange      E

CREATE TABLE SOME_TABLE
  ( ST_Field1 VARCHAR(100),
    ST_Field2 VARCHAR(100)
  );

INSERT INTO SOME_TABLE VALUES ('Apple','A');
INSERT INTO SOME_TABLE VALUES ('Apple','A');
INSERT INTO SOME_TABLE VALUES ('Apple','D');
INSERT INTO SOME_TABLE VALUES ('Orange','D');
INSERT INTO SOME_TABLE VALUES ('Orange','E');
INSERT INTO SOME_TABLE VALUES ('Orange','Z');
INSERT INTO SOME_TABLE VALUES ('Pear','D');
INSERT INTO SOME_TABLE VALUES ('Pear','G');
INSERT INTO SOME_TABLE VALUES ('Pear','C');

CREATE TABLE REFERENCE_TABLE
  ( RT_Field1 INTEGER,
    RT_Field2 VARCHAR(100)
  );

INSERT INTO REFERENCE_TABLE VALUES (1,'A');
INSERT INTO REFERENCE_TABLE VALUES (1,'B');
INSERT INTO REFERENCE_TABLE VALUES (1,'C');
INSERT INTO REFERENCE_TABLE VALUES (2,'D');
INSERT INTO REFERENCE_TABLE VALUES (2,'E');
INSERT INTO REFERENCE_TABLE VALUES (2,'F');
INSERT INTO REFERENCE_TABLE VALUES (3,'G');

可以假设RT\u Field2是唯一的。

所以从参考表{A,B,C}是一个分组。我想看看对于给定的stu字段1,是否可以看到{a,B};{B,c},{a,c}。我看不到,我看到A和C出现了,但是苹果和梨都出现了。

我有:

WITH DUP_VALUES_RTF2 AS
  ( SELECT * 
      FROM (SELECT DST.ST_Field1,
                   DST.ST_Field2,
                   COUNT(1) OVER (PARTITION BY RT.RT_Field1) cnt_RTF1
              FROM (SELECT DISTINCT
                           ST_Field1,
                           ST_Field2
                      FROM Some_Table
                   ) DST
             INNER
              JOIN REFERENCE_TABLE RT
                ON DST.ST_Field2 = RT.RT_Field2
           ) TMP
     WHERE cnt_RTF1 > 1
  )
SELECT * 
  FROM SOME_TABLE ST
 WHERE EXISTS
         ( SELECT 1
             FROM DUP_VALUES_RTF2 DVR
            WHERE ST.ST_Field1 = DVR.ST_Field1
              AND ST.ST_Field2 = DVR.ST_Field2
         );

因为它根本不能正确处理分组,而且非常难看。也许下午5点以后我就要脑死亡了。

2 回复 | 直到 5 年前

Gordon Linoff 6 年前

st_field1 rt_field1 ,在中查找重复项 rt_field2 . 您可以为此使用窗口函数:

select s.*
from (select s.*, rt.rt_field1, rt.rt_field2,
             min(rt.rt_field2) over (partition by s.st_field1, r.rt_field1) as min_rt2,
             max(rt.rt_field2) over (partition by s.st_field1, r.rt_field1) as max_rt2             
      from sometable s join
           reference_table r
           on s.st_field2 = r.rt_field2
     ) s
where min_rt2 <> max_rt2;

DhruvJoshi 6 年前

; with distinctSet as 
(select distinct s.*,RT_Field1 from SOME_TABLE s join REFERENCE_TABLE r on s.ST_Field2=r.RT_Field2
)
, 
uniqueSet as 
(
select RT_Field1,ST_Field1 
from distinctSet
group by RT_Field1,ST_Field1
having count(1)>1
),
resultSet as 
(
select
distinct 
s.*
from SOME_TABLE s 
join REFERENCE_TABLE r 
on s.ST_Field2=r.RT_Field2
join uniqueSet u
on u.RT_Field1=r.RT_Field1
and u.ST_Field1=s.ST_Field1
)
select * from resultSet