Блог

Ищу некоторые указания о том, какие варианты я должен рассмотреть для следующей проблемы создания инструкции select (SQL SERVER) на основе условий:

ПРИМЕЧАНИЕ: упрощение реальной проблемы

Если у меня есть две таблицы, в каждой из которых есть поле, мне нужно использовать EXAMPLE:

 CUSTOMER.CUSTOMER_ID
PRODUCT.PRODUCT_ID
  

У меня есть 2 другие таблицы, которые содержат специальные цены на продукты на основе этих двух полей
ПРИМЕР:

 CUSTOMER_PRICING 
***************************************
* CUSTOMER_ID  * PRODUCT_ID  * PRICE  *
*                                     *
*     ABC      *    100      *  5.00  *
***************************************

MARKET_PRICING 
***************************************
* CUSTOMER_ID  * PRODUCT_ID  * PRICE  *
*                                     *
*     ACME      *    200      *  7.00 *
***************************************
  

Для каждого CUSTOMER.CUSTOMER_ID и ПРОДУКТА.PRODUCT_ID Я хочу объединить записи CUSTOMER_PRICING и MARKET_PRICING, используя левое внешнее соединение. Но условия выполняются только в том случае, если

A) поле PRICE не равно null

Б) и если идентификатор CUSTOMER_ID / PRODUCT_ID существует в обоих, мне нужна только запись из CUSTOMER_PRICE

Что-то вроде:

 IF EXISTS (SELECT C.CUSTOMER_ID, P.PRODUCT_ID FROM CUSTOMER C, PRODUCT P 
           WHERE C.CUSTOMER_ID, P.PRODUCT_ID IN 
           (SELECT PRICE FROM CUSTOMER_PRICE WHERE PRICE IS NOT NULL))
ELSE IF EXISTS 
          (SELECT C.CUSTOMER_ID, P.PRODUCT_ID FROM CUSTOMER C, PRODUCT P 
           WHERE C.CUSTOMER_ID, P.PRODUCT_ID IN 
           (SELECT PRICE FROM MARKET_PRICE WHERE PRICE IS NOT NULL))
  

*** затем каким-то образом выполните объединение для этих результатов…..

Есть предложения?

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

CREATE VIEW CMPricing
AS

SELECT
t1.CUSTOMER_ID,
t1.PRODUCT_ID,
t1.PRICE AS CUSTOMER_PRICE
t2.PRICE AS MARKET_PRICE

FROM CUSTOMER_PRICING t1
INNER JOIN MARKET_PRICING t2
ON t1.CUSTOMER_ID = t2.CUSTOMER_ID
AND t1.PRODUCT_ID = t2.PRODUCT_ID

WHERE t1.PRICE IS NOT NULL 
AND t2.PRICE IS NOT NULL

Затем вы запрашиваете это представление, чтобы получить то, что вы хотите.

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

Вы могли бы объединить перекрестное произведение CUSTOMER и PRODUCT с помощью обоих CUSTOMER_PRICING и MARKET_PRICING , отфильтровав строки, в которых оба CUSTOMER_PRICING.PRICE и MARKET_PRICING.PRICE равны нулю. Это может выглядеть примерно так:

 SELECT
  c.CUSTOMER_ID,
  p.PRODUCT_ID,
  COALESCE(cp.PRICE, mp.PRICE) AS PRICE
FROM CUSTOMER c
  CROSS JOIN PRODUCT p
  LEFT JOIN CUSTOMER_PRICING cp ON cp.CUSTOMER_ID = c.CUSTOMER_ID AND cp.PRODUCT_ID = p.PRODUCT_ID
  LEFT JOIN MARKET_PRICING   mp ON mp.CUSTOMER_ID = c.CUSTOMER_ID AND mp.PRODUCT_ID = p.PRODUCT_ID
WHERE cp.PRICE IS NOT NULL OR mp.PRICE IS NOT NULL
-- Or, put differently:
-- WHERE NOT (cp.PRICE IS NULL AND mp.PRICE IS NULL)
  

Я думаю, что нашел решение. Я уверен, что эксперт по SQL мог бы придумать что-нибудь получше, но это должно заставить вас двигаться:

 WITH CTE AS
(
    SELECT C.CUSTOMER_ID, C.PRODUCT_ID, C.CUSTOMER_PRICE as [PRICE], 1 as [T]
    FROM CUSTOMER_PRICE C WHERE C.CUSTOMER_PRICE IS NOT NULL
    UNION
    SELECT P.CUSTOMER_ID, P.PRODUCT_ID,P.MARKET_PRICE as [PRICE], 2 as [T]
    FROM MARKET_PRICE P WHERE P.MARKET_PRICE IS NOT NULL
)
select CUSTOMER_ID,PRODUCT_ID,
    (
        SELECT TOP (1) PRICE FROM CTE i WHERE i.CUSTOMER_ID = o.CUSTOMER_ID AND i.PRODUCT_ID = o.PRODUCT_ID
        ORDER BY T
    ) as [PRICE]
from CTE o
GROUP BY CUSTOMER_ID,PRODUCT_ID
  

Это даст вам отдельный список идентификаторов клиентов, идентификаторов продуктов и цен; предпочтение отдается CUSTOMER_PRICE цене.

РЕДАКТИРОВАТЬ Это будет работать только с базами данных SQL 2008 и выше. В нем используются общие табличные выражения, которые являются новыми конструкциями для 2008.

ОБНОВИТЬ Исправленное предложение where.

Если вы используете ORACLE, вам следует использовать union для устранения повторяющихся строк и выполнения 2 отдельных запросов.