Получить общие записи между двумя таблицами на основе условия

Допустим, у меня есть две таблицы: ПромышленностьКлиенты и ПродуктКлиенты, у них одинаковая схема и всего один такой столбец.

ПромышленностьКлиенты:

Пользовательский ИД
1
2
3

ПродуктКлиенты:

Пользовательский ИД
2
3
4

Итак, что я хочу:

1- если и IndustryCustomers, и productCustomers имеют записи, тогда получите общих клиентов между ними (просто путем внутреннего соединения по customerId)

2- если у IndustryCustomers есть какие-либо записи, но у productCustomer нет записей, выберите все IndustryCustomers

3- если у IndustryCustomers нет записей, выберите всех клиентов продукта

В настоящее время я сделал это, используя IF и выбирая на основе условий, но мне интересно, смогу ли я получить клиентов с помощью одного запроса.

это мой запрос

IF EXISTS (SELECT TOP 1 1 FROM #IndustryCustomers)
BEGIN
    IF EXISTS (SELECT TOP 1 1 FROM #ProductCustomers)
        SELECT *
        FROM #IndustryCustomers ic
            JOIN #ProductCustomers pc
                ON ic.CustomerId = pc.CustomerId;
    ELSE
        SELECT *
        FROM #IndustryCustomers;
END;
ELSE
    SELECT *
    FROM #ProductCustomers;

Так в чем твой вопрос? Что ты пытался ответить на вопрос? Почему эти попытки не сработали? Где ты застрял в выполнении ваших требований?

— 18.03.2022 11:58

Вопрос в том, что я хочу получать записи по одному запросу. В настоящее время я сделал это по нескольким запросам.

— 18.03.2022 11:59

похоже, вы можете попробовать FULL JOIN между этими двумя таблицами

— 18.03.2022 11:59

Это не вопрос, @SaeedEsmaeelinejad. «Хочу» — это просьба. «В настоящее время я сделал это с помощью нескольких запросов» И что это за запросы? Почему ты не можешь их использовать?

— 18.03.2022 11:59

@Сергей да, но я думаю, что это не может выполнить условие 2 и 3

— 18.03.2022 12:00

@Larnu, вы правы, я обновлю вопрос тем, что я пробовал, я уже использовал запрос, и он работает, но я думаю, что запрос не очень хорош, и я ищу лучшее решение.

— 18.03.2022 12:06

Вам НЕ нужно использовать TOP в предложении EXISTS. Ядро базы данных перестанет «смотреть», когда найдет первую совпадающую строку. Научитесь оптимизировать написание собственного кода.

— 18.03.2022 12:18

sql sql-server tsql

18.03.2022 11:57

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Вы можете UNION ALL свои три SELECT и поместить соответствующее условие в предложение WHERE, например.

SELECT ic.CustomerId 
  FROM #IndustryCustomers AS ic 
       INNER JOIN #ProductCustomers AS pc ON ic.CustomerId = pc.CustomerId
 WHERE EXISTS (SELECT 1 FROM #IndustryCustomers) 
   AND EXISTS (SELECT 1 FROM #ProductCustomers)

UNION ALL 

SELECT ic.CustomerId 
  FROM #IndustryCustomers AS ic
 WHERE EXISTS (SELECT 1 FROM #IndustryCustomers) 
   AND NOT EXISTS (SELECT 1 FROM #ProductCustomers)

UNION ALL 

SELECT pc.CustomerId 
  FROM #ProductCustomers AS pc
 WHERE NOT EXISTS (SELECT 1 FROM #IndustryCustomers)

Очевидно, что для этого требуется, чтобы все три SQL-запроса возвращали один и тот же набор столбцов, поэтому я сократил * до идентификатора клиента.

Однако я думаю, что это "решение", формально удовлетворяющее вашим требованиям, менее читабельно, чем ваше текущее решение...

Это тоже работает, но я не хочу этого делать, но спасибо за предложение, что я не подумал об использовании UNION ALL и +1

— 18.03.2022 12:37

18.03.2022 12:17

TLDR;

SELECT
    *
FROM    (
    SELECT
        CustomerID  = CASE
                        WHEN NOT EXISTS (SELECT     * FROM ProductCustomers) THEN
                            ic.CustomerID
                        WHEN NOT EXISTS (SELECT     * FROM IndustryCustomers) THEN
                            pc.CustomerID
                        WHEN EXISTS (SELECT     * FROM IndustryCustomers)
                            AND EXISTS (SELECT      * FROM ProductCustomers)
                            AND ic.CustomerID = pc.CustomerID THEN
                            ic.CustomerID
                    END
    FROM    IndustryCustomers   AS ic
    FULL JOIN ProductCustomers AS pc
        ON pc.CustomerID = ic.CustomerID
) AS x
WHERE   x.CustomerID IS NOT NULL;

Разбивка запроса

Шаг 1: Получите все данные

Если вам нужен один запрос, но вы не хотите использовать UNION, вам нужно будет сделать FULL JOIN из двух таблиц:

SELECT
    *
FROM    IndustryCustomers   AS ic
FULL JOIN ProductCustomers AS pc
    ON pc.CustomerID = ic.CustomerID;

ic.CustomerID	pc.CustomerID
2	2
3	3
НУЛЕВОЙ	4
1	НУЛЕВОЙ

Шаг 2. Отфильтруйте данные в списке выбора на основе вашей логики.

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

SELECT
    CustomerID  = CASE
                    WHEN NOT EXISTS (SELECT     * FROM ProductCustomers) THEN
                        ic.CustomerID
                    WHEN NOT EXISTS (SELECT     * FROM IndustryCustomers) THEN
                        pc.CustomerID
                    WHEN EXISTS (SELECT     * FROM IndustryCustomers)
                        AND EXISTS (SELECT      * FROM ProductCustomers)
                        AND ic.CustomerID = pc.CustomerID THEN
                        ic.CustomerID
                END
FROM    IndustryCustomers   AS ic
FULL JOIN ProductCustomers AS pc
    ON pc.CustomerID = ic.CustomerID;

Пользовательский ИД
2
3
НУЛЕВОЙ
НУЛЕВОЙ

Шаг 3. Очистите результаты, удалив NULLS

Это дает вам желаемые результаты, но теперь у вас есть NULL для строк в наборе результатов, которые не соответствуют вашим критериям. У вас есть два варианта избавиться от них:

Опция 1

Скопируйте свое утверждение CASE в предложение WHERE и используйте его, чтобы отфильтровать NULLs.

Плюсы: Имеется один оператор SELECT. Здесь нет реальной пользы, если вы просто не предпочитаете, как это выглядит.

Минусы: Код сложнее читать, и если вы позже измените эту логику, вы должны помнить об обновлении логики в обоих местах. ИМХО, афера в этом большая афера. Вероятность того, что это произойдет, высока. Я вижу, что это происходит все время, когда люди быстро обновляют код.

SELECT
    CustomerID  = CASE
                    WHEN NOT EXISTS (SELECT     * FROM ProductCustomers) THEN
                        ic.CustomerID
                    WHEN NOT EXISTS (SELECT     * FROM IndustryCustomers) THEN
                        pc.CustomerID
                    WHEN EXISTS (SELECT     * FROM IndustryCustomers)
                        AND EXISTS (SELECT      * FROM ProductCustomers)
                        AND ic.CustomerID = pc.CustomerID THEN
                        ic.CustomerID
                END
FROM    IndustryCustomers   AS ic
FULL JOIN ProductCustomers AS pc
    ON pc.CustomerID = ic.CustomerID
WHERE   (CASE
            WHEN NOT EXISTS (SELECT * FROM ProductCustomers) THEN
                ic.CustomerID
            WHEN NOT EXISTS (SELECT * FROM IndustryCustomers) THEN
                pc.CustomerID
            WHEN EXISTS (SELECT * FROM IndustryCustomers)
                AND EXISTS (SELECT * FROM ProductCustomers )
                AND ic.CustomerID = pc.CustomerID THEN
                ic.CustomerID
        END
        )   IS NOT NULL;

Вариант 2

Оберните свой запрос запросом, который устраняет NULLS.

Плюсы: Не нужно поддерживать дублированную логику, более короткий и удобный для чтения код.

Минусы: Это не единичное SELECT утверждение, но функционально минусов нет.

SELECT
    *
FROM    (
    SELECT
        CustomerID  = CASE
                        WHEN NOT EXISTS (SELECT     * FROM ProductCustomers) THEN
                            ic.CustomerID
                        WHEN NOT EXISTS (SELECT     * FROM IndustryCustomers) THEN
                            pc.CustomerID
                        WHEN EXISTS (SELECT     * FROM IndustryCustomers)
                            AND EXISTS (SELECT      * FROM ProductCustomers)
                            AND ic.CustomerID = pc.CustomerID THEN
                            ic.CustomerID
                    END
    FROM    IndustryCustomers   AS ic
    FULL JOIN ProductCustomers AS pc
        ON pc.CustomerID = ic.CustomerID
) AS x
WHERE   x.CustomerID IS NOT NULL;

Пример кода, показывающий результаты для каждого сценария

Я использую Общее табличное выражение (CTE) и Конструктор табличных значений для построения данных примера. Запрос, который выбирает данные, один и тот же в каждом из них.

`IndustryCustomers` и `ProductCustomers` оба имеют данные

WITH
    IndustryCustomers AS (
        SELECT
            IndustryCustomers.CustomerID
        FROM ( VALUES (1), (2), (3)) AS IndustryCustomers (CustomerID)
    ),
    ProductCustomers AS (
        SELECT
            ProductCustomers.CustomerID
        FROM ( VALUES (2), (3), (4)) AS ProductCustomers (CustomerID)
    )
SELECT
    *
FROM    (
    SELECT
        CustomerID  = CASE
                        WHEN NOT EXISTS (SELECT     * FROM ProductCustomers) THEN
                            ic.CustomerID
                        WHEN NOT EXISTS (SELECT     * FROM IndustryCustomers) THEN
                            pc.CustomerID
                        WHEN EXISTS (SELECT     * FROM IndustryCustomers)
                            AND EXISTS (SELECT      * FROM ProductCustomers)
                            AND ic.CustomerID = pc.CustomerID THEN
                            ic.CustomerID
                    END
    FROM    IndustryCustomers   AS ic
    FULL JOIN ProductCustomers AS pc
        ON pc.CustomerID = ic.CustomerID
) AS x
WHERE   x.CustomerID IS NOT NULL;

Пользовательский ИД
2
3

`ProductCustomers` не содержит данных

WITH
    IndustryCustomers AS (
        SELECT
            IndustryCustomers.CustomerID
        FROM ( VALUES (1), (2), (3)) AS IndustryCustomers (CustomerID)
    ),
    ProductCustomers AS (
        SELECT CustomerID = NULL
        WHERE 1 = 2
    )
SELECT
    *
FROM    (
    SELECT
        CustomerID  = CASE
                        WHEN NOT EXISTS (SELECT     * FROM ProductCustomers) THEN
                            ic.CustomerID
                        WHEN NOT EXISTS (SELECT     * FROM IndustryCustomers) THEN
                            pc.CustomerID
                        WHEN EXISTS (SELECT     * FROM IndustryCustomers)
                            AND EXISTS (SELECT      * FROM ProductCustomers)
                            AND ic.CustomerID = pc.CustomerID THEN
                            ic.CustomerID
                    END
    FROM    IndustryCustomers   AS ic
    FULL JOIN ProductCustomers AS pc
        ON pc.CustomerID = ic.CustomerID
) AS x
WHERE   x.CustomerID IS NOT NULL;

Пользовательский ИД
1
2
3

`IndustryCustomers` не содержит данных

WITH
    IndustryCustomers AS (
        SELECT CustomerID = NULL
        WHERE 1 = 2
    ),
    ProductCustomers AS (
        SELECT
            ProductCustomers.CustomerID
        FROM ( VALUES (2), (3), (4)) AS ProductCustomers (CustomerID)
    )
SELECT
    *
FROM    (
    SELECT
        CustomerID  = CASE
                        WHEN NOT EXISTS (SELECT     * FROM ProductCustomers) THEN
                            ic.CustomerID
                        WHEN NOT EXISTS (SELECT     * FROM IndustryCustomers) THEN
                            pc.CustomerID
                        WHEN EXISTS (SELECT     * FROM IndustryCustomers)
                            AND EXISTS (SELECT      * FROM ProductCustomers)
                            AND ic.CustomerID = pc.CustomerID THEN
                            ic.CustomerID
                    END
    FROM    IndustryCustomers   AS ic
    FULL JOIN ProductCustomers AS pc
        ON pc.CustomerID = ic.CustomerID
) AS x
WHERE   x.CustomerID IS NOT NULL;

Пользовательский ИД
2
3
4

Дополнительные примечания

При использовании предложения EXISTS всегда используйте форму SELECT * FROM .... Мало того, что цель кода более понятна, но нет разницы в производительности между использованием *, 1, TOP 1 1 или Column1, ..., Column327. SQL Server прекращает выполнение запроса, как только находит единственный результат, и даже не рассматривает ТОП. Если вы сравните их, вы увидите, что все планы выполнения идентичны.

EXISTS (SELECT 1...) vs EXISTS (SELECT TOP 1...) Имеет ли это значение?

Протестируйте с 10 000 записей в каждой таблице, и только половина из них перекрывается

SET STATISTICS IO, TIME ON
DECLARE
    @IndustryStartID    int = 1,
    @IndustryEndID      int = 10,
    @ProductStartID     int = 5,
    @ProductEndID       int = 15;


WITH
    IndustryCustomers AS (
        SELECT CustomerID = @IndustryStartID
        UNION ALL
        SELECT
            ic.CustomerID + 1
        FROM    IndustryCustomers AS ic
        WHERE   ic.CustomerID + 1 <= @IndustryEndID
    ),
    ProductCustomers AS (
        SELECT CustomerID = @ProductStartID
        UNION ALL
        SELECT
            pc.CustomerID + 1
        FROM    ProductCustomers AS pc
        WHERE   pc.CustomerID + 1 <= @ProductEndID
    )
SELECT
    *
FROM    (
    SELECT
        CustomerID  = CASE
                        WHEN NOT EXISTS (SELECT     * FROM ProductCustomers) THEN
                            ic.CustomerID
                        WHEN NOT EXISTS (SELECT     * FROM IndustryCustomers) THEN
                            pc.CustomerID
                        WHEN EXISTS (SELECT     * FROM IndustryCustomers)
                            AND EXISTS (SELECT      * FROM ProductCustomers)
                            AND ic.CustomerID = pc.CustomerID THEN
                            ic.CustomerID
                    END
    FROM    IndustryCustomers   AS ic
    FULL JOIN ProductCustomers AS pc
        ON pc.CustomerID = ic.CustomerID
) AS x
WHERE   x.CustomerID IS NOT NULL
OPTION (MAXRECURSION 10000);

SET STATISTICS IO, TIME OFF

Спасибо за ваш ответ, но я думаю, что у этого запроса есть проблемы с производительностью.

— 18.03.2022 19:31

Проблемы с производительностью? Нравится? SQL Server достаточно умен, чтобы знать, что запросы в предложении EXISTS нужно запускать только один раз, и, поскольку они являются предложениями EXISTS, не имеют критериев, они должны быть невероятно быстрыми. FULL JOIN никогда не может вернуть больше строк, чем сумма строк в обеих таблицах. Поэтому, если в этих двух таблицах нет огромного количества записей, а в каждой строке МНОГО столбцов, это тоже не должно быть проблемой. В этом случае вам необходимо создать индекс для столбца CustomerID и включить в этот индекс любые дополнительные столбцы, которые вы хотите вернуть.

— 18.03.2022 19:48

Да, предложение существования выполняется быстро, но в случае использования при выборе, что означает, что оно выполняется для каждой строки, и если есть 10 000 строк, оно будет выполнять 10 000, предполагая невозможность, если существует занимает 1 мс, тогда для запуска требуется 10 секунд.

— 18.03.2022 19:59

Вы действительно проверяли это? Я только что провел тест с идентификаторами IndustryCustomer от 1 до 10 000 и ProductCustomers с идентификаторами от 5 000 до 15 000, и он выполнялся за четверть секунды: Время выполнения SQL Server: время ЦП = 204 мс, истекшее время = 214 мс.

— 18.03.2022 20:28

Это показывает, что JOIN составляет 100% стоимости в плане выполнения. Вы можете получить разные результаты с данными, поступающими из реальных таблиц, но они не должны сильно отличаться, но производительность конкретного запроса сильно зависит от специфики схемы и среды сервера базы данных. Я добавил любой пример, показывающий тот же запрос с 10 000 записей в каждой таблице. Это все еще молниеносно.

— 18.03.2022 20:35

Спасибо, Ник, я должен проверить это по вашему последнему запросу, но с вашей статистикой это должно быть интересно. +1

— 18.03.2022 20:51

18.03.2022 15:56

Ответ принят как подходящий

Лично я, вероятно, использовал бы подход @Heinz, но, как ни странно, NOT EXISTS работали хуже, чем мое решение. Согласно плану выполнения, кажется, что NOT EXISTS сканировал всю таблицу без всякой причины, не знаю почему. Придется дополнительно исследовать, что происходит (я использую SQL Server 2017 dev edition).

Итак, вот очень краткое решение, которое, кажется, работает лучше, чем решения Хайнци и Ника (в моем очень ограниченном тестировании).

Краткое решение с использованием APPLY и полного соединения

SELECT FinalCustomerID = ISNULL(I.CustomerID,P.CustomerID)
FROM #IndustryCustomers AS I
FULL JOIN #ProductCustomers AS P
    ON I.CustomerID = P.CustomerID
CROSS APPLY (
    SELECT 
     HasI = CASE WHEN EXISTS (SELECT * FROM #IndustryCustomers) THEN 'Y' ELSE 'N' END
    ,HasP = CASE WHEN EXISTS (SELECT * FROM #ProductCustomers ) THEN 'Y' ELSE 'N' END
) AS C
WHERE ('N' NOT IN (HasI,HasP) AND I.CustomerID = P.CustomerID)
    OR (HasI = 'Y' AND HasP = 'N' AND I.CustomerID IS NOT NULL)
    OR (HasI = 'N' AND HasP = 'Y' AND P.CustomerID IS NOT NULL)

Что касается производительности, очевидно, что SQL Server 2017 не кэширует результаты некоррелированного подзапроса, поэтому он выполняется для каждой строки в левой таблице. Вот почему мое решение, кажется, превосходит другие решения от Heinzi и Nick. Для их запросов, если у вас есть 5000 строк в #Industry и всего 1 строка в #Product, их запрос будет сканировать #Product 5000 раз SELECT * FROM #IndustryCustomers WHERE NOT EXISTS (SELECT * FROM #ProductCustomers)

— 23.03.2022 16:52

23.03.2022 06:53