Arhn - архитектура программирования

SQL Сравнение 2 таблиц при ограничении данных

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

У меня есть две такие таблицы на MSQL Server 2008:

Table 1

ProductID, Description, Vendor, Price

Table 2

ProductID, Description, Price

Я хочу выбрать каждую строку из Table 2 и соответствующие данные из Table 1. Но я хочу вернуть только продавца с лучшей ценой (самая низкая цена среди продавцов) от Table 1, а не каждого продавца. Таким образом, для любого ProductID в Table 2 должно быть одно совпадение из Table1 или значение NULL, если нет совпадения ProductID в Table 1. Я присоединился к таблицам ProductID и вернул все нужные столбцы, но не могу заставить его ограничиться только одним результатом из таблицы 1. Если я сделаю это с 1000 строками в Table 2, я должен вернуть 1000 строк. Я продолжаю заканчивать с несколькими дополнительными совпадениями от нескольких поставщиков.

Результаты должны выглядеть следующим образом:

T1.ProductID, T1.Description, Vendor, T1.Price, T2.ProductID,
T2.Description, T2.Price, (T2.Price - T1.Price) as 'Amount Saved'

SQL, который я написал, довольно прост:

SELECT 
    T1.ProductID, 
    T1.Description, 
    Vendor, 
    T1.Price, 
    T2.ProductID, 
    T2.Description,
    T2.Price, 
    (T2.Price - T1.Price) AS 'Amount Saved'

FROM 
    Table2 T2 LEFT OUTER JOIN Table1 T1 
        ON T2.ProductID = T1.ProductID
ORDER BY T2.ProductID

Этот ответ Д. Стэнли сработал; с небольшим изменением, чтобы выбрать каждую строку с самой низкой ценой.

SELECT 
    T1.ProductID,
    T1.Description,
    T1.Vendor,
    T1.Price,
    T2.ProductID,
    T2.Description, 
    T2.Price, 
   (T1.Price - T2.Price) as 'Amount Saved'
FROM Table2 T2
LEFT JOIN (
    SELECT * FROM (
       SELECT ProductID, Description, Vendor, Price,
       ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Price ASC) AS Row
       FROM Table1) as result
    WHERE row=1
    )  AS T1
    ON T2.ProductID = T1.ProductID
sql sql-server-2008 select
27.06.2013

  • Можете ли вы опубликовать запрос? 27.06.2013
  • какая лучшая цена, вы имеете в виду самую низкую (минимальную) цену, максимальную экономию и т. д.? пожалуйста, вставьте код, который вы пробовали 27.06.2013

Ответы:


1

Вы можете использовать ROW_NUMBER, чтобы найти «лучшую» совпадающую строку из Table1:

SELECT 
    T1.ProductID, 
    T1.Description, 
    T1.Vendor, 
    T1.Price, 
    T2.ProductID,
    T2.Description, 
    T2.Price, 
    (T1.Price - T2.Price) as 'Amount Saved'
    FROM Table2 T2
    LEFT JOIN (
        SELECT ProductID, Description, Vendor, Price,
           ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Price DESC) Row
           FROM Table1
        ) T1
        ON T2.ProductID = T1.ProductID
27.06.2013

2

Непонятно, что вы имеете в виду под "лучшей ценой". Просто настройте функцию MAX() на функцию MIN(), если это не то, что вам нужно.

SELECT T1.ProductID, T1.Description, Vendor, T1.Price, T2.ProductID,
T2.Description, T2.Price, (T1.Price - T2.Price) as 'Amount Saved'
FROM (
SELECT
ProductID, Vendor, Description, Price
FROM Table1 t
WHERE Price = (SELECT MAX(Price) FROM Table1 subT WHERE t.ProductID = subT.ProductID)
) T1 RIGHT JOIN 
Table2 T2 ON T1.ProductID = T2.ProductID
  • Вот кое-что Вы должны прочитать.

РЕДАКТИРОВАТЬ: сейчас я прочитал, что это для SQL Server. Решение, которое я предоставил, и дополнительные материалы для чтения работают и для SQL Server. Хотя это из руководства по MySQL, но здесь задействован только стандартный SQL.

27.06.2013

3

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

Если я ошибаюсь в названиях таблиц, сообщите мне, что это то, о чем я предполагал, что вы просили, потому что я не уверен на 100%, что вы подразумеваете под «лучшей ценой», но я предполагаю, что она была самой низкой на основе вашей «Сэкономленной суммы». поле, которое было выбрано.

SELECT
    DISTINCT(T1.ProductId) AS ProductId,
    Description,
    Vendor,
    Price,
    SupplyDescription,
    SupplyPrice,
    AmountSaved AS 'Amount Saved' 
FROM
(
    SELECT 
        T1.ProductId AS ProductId, 
        T1.Description,
        T2.Vendor,
        T1.Price, 
    /*  T2.ProductId you are joining on this no need to include again*/
        T2.Description AS SupplyDescription,
        T2.Price AS SupplyPrice ,
        (T1.Price - ISNULL(T2.Price,T1.Price)) as AmountSaved
    FROM
    Product T1 WITH(NOLOCK)
    LEFT OUTER JOIN Supply T2 WITH(NOLOCK)
        ON T1.ProductId = T2.ProductId
    HAVING T2.Price = MIN(T2.Price)
) tt

ПРИМЕЧАНИЕ. Предполагается, что таблицы Product и Supply не обновляются динамически в течение дня или изменяются очень редко, если это не так (таблицы Product и Supply обновляются часто), и вы не можете оправдать грязные чтения, вызванные избеганием блокировки, просто удалить подсказки WITH(NOLOCK) из запроса

27.06.2013
Новые материалы
Коллекции публикаций по глубокому обучению
Последние пару месяцев я создавал коллекции последних академических публикаций по различным подполям глубокого обучения в моем блоге https://amundtveit.com - эта публикация дает обзор 25..
Представляем: Pepita
Фреймворк JavaScript с открытым исходным кодом Я знаю, что недостатка в фреймворках JavaScript нет. Но я просто не мог остановиться. Я хотел написать что-то сам, со своими собственными..
Советы по коду Laravel #2
1-) Найти // You can specify the columns you need // in when you use the find method on a model User::find(‘id’, [‘email’,’name’]); // You can increment or decrement // a field in..
Работа с временными рядами спутниковых изображений, часть 3 (аналитика данных)
Анализ временных рядов спутниковых изображений для данных наблюдений за большой Землей (arXiv) Автор: Рольф Симоэс , Жильберто Камара , Жильберто Кейрос , Фелипе Соуза , Педро Р. Андраде ,..
3 способа решить квадратное уравнение (3-й мой любимый) -
1. Методом факторизации — 2. Используя квадратичную формулу — 3. Заполнив квадрат — Давайте поймем это, решив это простое уравнение: Мы пытаемся сделать LHS,..
Создание VR-миров с A-Frame
Виртуальная реальность (и дополненная реальность) стали главными модными терминами в образовательных технологиях. С недорогими VR-гарнитурами, такими как Google Cardboard , и использованием..
Демистификация рекурсии
КОДЕКС Демистификация рекурсии Упрощенная концепция ошеломляющей О чем весь этот шум? Рекурсия, кажется, единственная тема, от которой у каждого начинающего студента-информатика..