Как выбрать значения из двух таблиц, которые не содержатся в таблице карты?

Допустим, у меня есть следующие таблицы:

  • Клиенты
  • Товары
  • КлиентПродукты

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

Еще один поворот: мне нужно связать одного клиента с каждым продуктом. Таким образом, если у 5 клиентов нет продукта А, только первый клиент в запросе должен иметь продукт А. Таким образом, результаты будут выглядеть примерно так:

(Предположим, что все клиенты владеют продуктом B, и несколько клиентов владеют продуктами A, C и D)

  1. Клиент 1, Продукт А
  2. Клиент 2, продукт C
  3. Клиент 3, продукт D

Последний штрих: мне нужно запустить этот запрос как часть оператора UPDATE в SQL Sever. Поэтому мне нужно взять значение из первой строки:

Клиент 1, Продукт А

и обновите запись клиента на что-то вроде

UPDATE Customers
SET Customers.UnownedProduct = ProductA
WHERE Customers.CustomerID = Customer1ID

Но было бы неплохо, если бы я мог выполнить весь этот процесс в одном операторе SQL. Итак, я запускаю запрос один раз, и он сообщает 1 покупателю о продукте, которым он не владеет. Надеюсь, это не слишком запутанно для вас! Заранее спасибо!


sql sql-server stored-procedures subquery nested-queries
person John B    schedule 18.03.2009    source источник
comment
Что, если у вас есть два клиента и только один продукт, которым они оба не владеют?   -  person Peter Lang    schedule 18.03.2009
comment
Тогда запрос должен вернуть только 1 запись с первым клиентом.   -  person John B    schedule 18.03.2009

Ответы (4)


arrow_upward
3
arrow_downward 

WITH q AS
        (
        SELECT  c.*, p.id AS Unowned,
                ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY c.id) AS rn
        FROM    Customers c
        CROSS JOIN
                Products p
        LEFT JOIN 
                CustomerProducts cp
        ON      cp.customer = c.id
                AND cp.product = p.id
        WHERE   cp.customer IS NULL
        )
UPDATE  q
SET     UnownedProduct = Unowned
WHERE   rn = 1

UPDATE обновит первого клиента, у которого нет определенного продукта.

Если вы хотите выбрать список, вам понадобятся:

SELECT  *
FROM    (
        SELECT  c.*, p.id AS Unowned,
                ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY c.id) AS rn
        FROM    Customers c
        CROSS JOIN
                Products p
        LEFT JOIN 
                CustomerProducts cp
        ON      cp.customer = c.id
                AND cp.product = p.id
        WHERE   cp.customer IS NULL
    ) cpo
WHERE   rn = 1
person Quassnoi    schedule 18.03.2009
comment
Это не принимает во внимание еще один и последний поворот OP ?! - person Lieven Keersmaekers; 18.03.2009
comment
Это учитывает оба :) - person Quassnoi; 18.03.2009

arrow_upward
0
arrow_downward

Если вы обновляете только одного клиента одновременно, вам может потребоваться запомнить, какие продукты были назначены автоматически (в CustomerProducts), или иметь счетчик, как часто продукт назначался автоматически (в Products).

person Peter Lang    schedule 18.03.2009

arrow_upward
0
arrow_downward

Я попробовал это в оракуле (надеюсь, это сработает и для вас)

UPDATE customers c
   SET unownedProduct =
       ( SELECT MIN( productid )
           FROM products
          WHERE productid NOT IN (
              SELECT unownedProduct
                FROM customers
               WHERE unownedProduct IS NOT NULL )
            AND productid NOT IN (
              SELECT productid
                FROM customerProducts cp
               WHERE cp.customerId = c.customerid )
       )
 WHERE customerId = 1
person Peter Lang    schedule 18.03.2009

arrow_upward
0
arrow_downward

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

person HLGEM    schedule 18.03.2009