Блог

Главная - Вопросы по программированию - SQL запрос — проверка наличия двух различных значений

SQL запрос — проверка наличия двух различных значений

#sql #sqlite

#sql #sqlite

Вопрос:

Учитывая приведенный ниже набор данных, я хочу запустить запрос, чтобы выделить любые «пары», которые не состоят из «левого» и «правого».

  --------- ----------- --------------- ---------------------- 
| Pair_Id | Pair_Name | Individual_Id | Individual_Direction |
 --------- ----------- --------------- ---------------------- 
|       1 | A         | A1            | Left                 |
|       1 | A         | A2            | Right                |
|       2 | B         | B1            | Right                |
|       2 | B         | B2            | Left                 |
|       3 | C         | C1            | Left                 |
|       3 | C         | C2            | Left                 |
|       4 | D         | D1            | Right                |
|       4 | D         | D2            | Left                 |
|       5 | E         | E1            | Left                 |
|       5 | E         | E2            | Right                |
 --------- ----------- --------------- ----------------------

В этом случае пара 3 ‘C’ имеет два левых. Поэтому я бы хотел отобразить следующее:

  --------- ----------- --------------- ---------------------- 
| Pair_Id | Pair_Name | Individual_Id | Individual_Direction |
 --------- ----------- --------------- ---------------------- 
|       3 | C         |  C1           | Left                 |
|       3 | C         |  C2           | Left                 |
 --------- ----------- --------------- ----------------------

Ответ №1:

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

 select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.pair_id = t.pair_id and
                        t2.Individual_Direction <> t.Individual_Direction
                 ) ;

С включенным индексом (pair_id, Individual_Direction) это должно быть не только наиболее кратким решением, но и самым быстрым.

Если вы хотите быть уверены, что есть пары (приведенное выше возвращает синглтоны):

 select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.pair_id = t.pair_id and
                        t2.Individual_Direction <> t.Individual_Direction
                 ) and
      exists (select 1
              from t t2
              where t2.pair_id = t.pair_id and
                    t2.Individual_ID <> t.Individual_ID
            );

Вы также можете сделать это с помощью оконных функций:

 select t.*
from (select t.*,
             count(*) over (partition by pair_id) as cnt,
             min(status) over (partition by pair_id) as min_status,
             max(status) over (partition by pair_id) as max_status
      from t
     ) t
where cnt > 1 and min_status <> max_status;

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

1. У меня есть несколько пар в моей таблице, которые имеют только один экземпляр (т. Е. Только левый или правый) (в моем случае использования это нормально). Я хочу только идентифицировать дубликаты (т. Е. Два левых). Я думаю, что могу добиться этого, введя количество > 2?

2. @JHarley1: Добавить AND COUNT(*) > 1 к Тиму, ИМЕЮЩЕМУ

3. @JHarley1 . . . Ваш вопрос таков: «Учитывая приведенный ниже набор данных, я хочу выполнить запрос, чтобы выделить любые «пары», которые не состоят из «левого» и «правого».», поэтому два права будут совпадать. Я отредактировал вопрос, чтобы отфильтровать одиночные элементы. Также странно, что вы приняли ответ, который не соответствует этому условию.

4. @GordonLinoff, рад, что есть хороший способ решения проблемы с помощью оконной функции. однако есть 2 исправления: 1) должно быть Individual_Direction вместо status для имени столбца; 2) минимальный статус должен быть = max status, а не <>, так как он хочет только слева.

Ответ №2:

Один из вариантов использует агрегирование:

 WITH cte AS (
    SELECT Pair_Name
    FROM yourTable
    WHERE Individual_Direction IN ('Left', 'Right')
    GROUP BY Pair_Name
    HAVING MIN(Individual_Direction) = MAX(Individual_Direction)
)

SELECT *
FROM yourTable
WHERE Pair_Name IN (SELECT Pair_Name FROM cte);

HAVING Предложение, используемое выше, утверждает, что совпадающая пара имеет как минимальное, так и максимальное направление, которые одинаковы. Это означает, что такая пара имеет только одно направление.

Как и в случае с ответом Гордона, индекс на (Pair_Name, Individual_Direction) может повысить производительность:

 CREATE INDEX idx ON yourTable (Pair_Name, Individual_Direction);

Ответ №3:

Должен быть элегантный способ использования функции окна, чем то, что я написал:

 WITH ranked AS
(
 SELECT *, RANK() OVER(ORDER BY Pair_Id, Pair_Name, Individual_Direction) AS r
     FROM pairs
),
    counted AS
(
SELECT Pair_Id, Pair_Name, Individual_Direction,r, COUNT(r) as times FROM ranked 
GROUP BY Pair_Id, Pair_Name, Individual_Direction, r
HAVING COUNT(r) > 1
)
SELECT ranked.Pair_Id, ranked.Pair_Name, ranked.Individual_Id, ranked.Individual_Direction FROM ranked
RIGHT JOIN counted 
ON ranked.Pair_Id=counted.Pair_Id 
AND ranked.Pair_Name=counted.Pair_Name 
AND ranked.Individual_Direction=counted.Individual_Direction