Блог

Главная - Вопросы по программированию - Выбор повторяющихся строк с различными значениями столбцов

Выбор повторяющихся строк с различными значениями столбцов

#sql #oracle

Вопрос:

У меня есть представление со строками, которые могут содержать одного и того же человека несколько раз, но, возможно, с разными значениями в разных столбцах. Например:

 ColA   ColB    ColC      Name
------ ------- --------- -------------
497222 5471580 099228813 John Travolta
497222 5615311 099228813 John Travolta
497222 5385303 099228813 John Travolta
497222 5385303 081224467 Tracy Morgan
503373 5471580 033582700 Christine Taylor
503373 5241410 033582700 Christine Taylor
805836 2211223 066491720 Richard Dreyfuss
961922 4030548 066491720 Richard Dreyfuss
503373 5241410 881236698 Erika Eleniak
121214 7723841 072546815 Tilda Swinton
168463 9998994 072546815 Tilda Swinton

В своих результатах я хочу возвращать только строки, в которых есть несколько вхождений ColC значения, поэтому я бы никогда не хотел Tracy Morgan или Erika Eleniak в своих результатах.

То, что я ищу, — это два отдельных сценария:

  1. Верните три результата John Travolta и два Christine Taylor , основываясь на том, что каждый из них имеет кратные значения с одинаковым значением для ColA , но разными значениями для ColB .
  2. Верните два результата Richard Dreyfuss и два Tilda Swinton , основываясь на том, что каждый из них имеет разные значения ColA .

Благодаря коллеге, я думаю, что у меня есть хороший запрос для сценария 1:

 SELECT ColA, ColB, ColC, Name
FROM MyView
WHERE ColC IN
(
  SELECT ColC
  FROM
  (
    SELECT ColC, COUNT (*)
    FROM MyView
    GROUP BY ColC
    HAVING COUNT (ColC) > 1
  )
);

Однако я совершенно не понимаю, как справиться со сценарием 2, поэтому буду признателен за любую помощь!

Комментарии:

1. Вы не используете PL/SQL; PL/SQL-это процедурный язык Oracle. Вы просто используете SQL.

Ответ №1:

Вы можете выполнить свой первый запрос только с помощью сканирования одной таблицы:

 SELECT cola,
       colb,
       colc,
       name
FROM   (
  SELECT t.*,
         COUNT(DISTINCT colb) OVER (PARTITION BY colc, cola) AS num_b
  FROM   myview t
)
WHERE  num_b > 1;

Который, для выборочных данных:

 CREATE TABLE myview (cola, colb, colc, name ) AS
SELECT 497222, 5471580, 99228813, 'John Travolta' FROM DUAL UNION ALL
SELECT 497222, 5615311, 99228813, 'John Travolta' FROM DUAL UNION ALL
SELECT 497222, 5385303, 99228813, 'John Travolta' FROM DUAL UNION ALL
SELECT 497222, 5385303, 81224467, 'Tracy Morgan' FROM DUAL UNION ALL
SELECT 503373, 5471580, 33582700, 'Christine Taylor' FROM DUAL UNION ALL
SELECT 503373, 5241410, 33582700, 'Christine Taylor' FROM DUAL UNION ALL
SELECT 805836, 2211223, 66491720, 'Richard Dreyfuss' FROM DUAL UNION ALL
SELECT 961922, 4030548, 66491720, 'Richard Dreyfuss' FROM DUAL UNION ALL
SELECT 503373, 5241410, 881236698, 'Erika Eleniak' FROM DUAL UNION ALL
SELECT 121214, 7723841, 72546815, 'Tilda Swinton' FROM DUAL UNION ALL
SELECT 168463, 9998994, 72546815, 'Tilda Swinton' FROM DUAL

Выходы:

кола КОЛЬБ КОЛЬК Имя
503373 5241410 33582700 Кристин Тейлор
503373 5471580 33582700 Кристин Тейлор
497222 5385303 99228813 Джон Траволта
497222 5471580 99228813 Джон Траволта
497222 5615311 99228813 Джон Траволта

Для вашего второго запроса вы можете использовать ту же технику:

 SELECT cola,
       colb,
       colc,
       name
FROM   (
  SELECT t.*,
         COUNT(DISTINCT cola) OVER (PARTITION BY colc) AS num_a
  FROM   myview t
)
WHERE  num_a > 1;

Какие результаты:

кола КОЛЬБ КОЛЬК Имя
805836 2211223 66491720 Ричард Дрейфусс
961922 4030548 66491720 Ричард Дрейфусс
121214 7723841 72546815 Тильда Суинтон
168463 9998994 72546815 Тильда Суинтон

бд<>скрипка <>здесь

Ответ №2:

Вы можете использовать exists :

 select v.*
from myview v
where exists (select 1
              from myview v2
              where v2.name = v.name and
                    v2.colc <> v.colc
             );

Ответ №3:

 Please check this, Enforced all the conditions necessary for your scenarios that you mentioned.  

WITH MYVIEW AS 
(SELECT 497222  as colA,    5471580 as colB,    99228813    as colC,'John Travolta' AS NAME FROM DUAL UNION ALL
SELECT 497222   as colA,    5615311 as colB,    99228813    as colC,'John Travolta' AS NAME FROM DUAL UNION ALL
SELECT 497222   as colA,    5385303 as colB,    99228813    as colC,'John Travolta' AS NAME FROM DUAL UNION ALL
SELECT 497222   as colA,    5385303 as colB,    81224467    as colC,'Tracy Morgan' AS NAME FROM DUAL UNION ALL
SELECT 503373   as colA,    5471580 as colB,    33582700    as colC,'Christine Taylor' AS NAME FROM DUAL UNION ALL
SELECT 503373   as colA,    5241410 as colB,    33582700    as colC,'Christine Taylor' AS NAME FROM DUAL UNION ALL
SELECT 805836   as colA,    2211223 as colB,    66491720    as colC,'Richard Dreyfuss' AS NAME FROM DUAL UNION ALL
SELECT 961922   as colA,    4030548 as colB,    66491720    as colC,'Richard Dreyfuss' AS NAME FROM DUAL UNION ALL
SELECT 503373   as colA,    5241410 as colB,    881236698   as colC,'Erika Eleniak' AS NAME FROM DUAL UNION ALL
SELECT 121214   as colA,    7723841 as colB,    72546815    as colC,'Tilda Swinton' AS NAME FROM DUAL UNION ALL
SELECT 168463   as colA,    9998994 as colB,    72546815    as colC,'Tilda Swinton' AS NAME FROM DUAL)

--1ST Requirement
SELECT 'Req-1' as Req, cola, colb, colc, name FROM MYVIEW WHERE COLC IN (
SELECT COLC
FROM MYVIEW
GROUP BY COLC
HAVING COUNT(COLA)>1 AND COUNT(DISTINCT COLA)=1 AND COUNT(DISTINCT COLB)>1 AND COUNT(COLC)>1
)
--2nd Requirement
union all
SELECT 'Req-2' as Req, cola, colb, colc, name FROM MYVIEW WHERE COLC IN (
SELECT COLC
FROM MYVIEW
GROUP BY COLC
HAVING COUNT(DISTINCT COLA)>1 AND COUNT(COLC)>1
)
;

Ответ №4:

Вы хотите вернуть строку, когда у человека есть по крайней мере еще одна строка с тем же значением colc и либо другой cola, либо другой colb. Или так я понимаю эту просьбу. Для этого вы можете использовать EXISTS предложение.

 select *
from mytable
where exists
(
  select null
  from mytable other
  where other.rowid <> mytable.rowid
  and other.name = mytable.name
  and other.colc = mytable.colc
  and (other.cola <> mytable.cola or other.colb <> mytable.colb)
)
order by name, cola, colb, colc;

Ответ №5:

Я думаю, что следующий запрос удовлетворит всем трем вашим требованиям:

 WITH test_data (col_a, col_b, col_c, name) AS
(
  SELECT 497222, 5471580, 099228813, 'John Travolta' FROM DUAL UNION ALL
  SELECT 497222, 5615311, 099228813, 'John Travolta' FROM DUAL UNION ALL
  SELECT 497222, 5385303, 099228813, 'John Travolta' FROM DUAL UNION ALL
  SELECT 497222, 5385303, 081224467, 'Tracy Morgan' FROM DUAL UNION ALL
  SELECT 503373, 5471580, 033582700, 'Christine Taylor' FROM DUAL UNION ALL
  SELECT 503373, 5241410, 033582700, 'Christine Taylor' FROM DUAL UNION ALL
  SELECT 805836, 2211223, 066491720, 'Richard Dreyfuss' FROM DUAL UNION ALL
  SELECT 961922, 4030548, 066491720, 'Richard Dreyfuss' FROM DUAL UNION ALL
  SELECT 503373, 5241410, 881236698, 'Erika Eleniak' FROM DUAL UNION ALL
  SELECT 121214, 7723841, 072546815, 'Tilda Swinton' FROM DUAL UNION ALL
  SELECT 168463, 9998994, 072546815, 'Tilda Swinton' FROM DUAL
)
SELECT s.col_a, s.col_b, s.col_c, s.name
FROM (SELECT td.*, 
             COUNT(*) OVER (PARTITION BY td.name, col_c) AS COL_C_COUNT,
             COUNT(*) OVER (PARTITION BY td.name, col_a) AS COL_A_COUNT,
             COUNT(*) OVER (PARTITION BY td.name, col_a, col_b) AS COL_B_COUNT
      FROM test_data td) s
WHERE s.col_c_count > 1 --First check to see if there are multiple values for column C
AND (COL_A_COUNT = 1 --Checks for Different Values in Col A (Dreyfuss/Swinton Case)
     OR COL_B_COUNT = 1); --Checks for Uniqueness in Col B if COL_A_COUNT > 1 (Based on short-circuit)

Как уже упоминалось в комментариях, аналитические функции во внутреннем запросе предоставляют вам информацию, необходимую для того, чтобы вы могли определить, кого возвращать во внешнем запросе.

Метки: