Использование подзапроса или INNER JOIN
Пункт 2 задачи 2 этой лабораторной работы от команды Microsoft SQL Server выглядит следующим образом:
Задача 1: получить информацию о цене продукта
Каждый продукт AdventureWorks имеет стандартную себестоимость, обозначающую стоимость производства продукта, и знак list price, обозначающий рекомендуемую цену продажи продукта. Эти данные хранятся в таблице SalesLT.Product. Каждый раз, когда товар заказывается, в таблице actual unit price также записывается SalesLT.SalesOrderDetail, по которой он был продан. Вы должны использовать подзапросы для сравнения себестоимости и прейскурантных цен для каждого продукта с ценами за единицу, взимаемыми при каждой продаже.
Получите продукты, прейскурантная цена которых выше средней цены за единицу.
Получите идентификатор продукта, название и цену по прейскуранту для каждого продукта, цена которого превышает среднюю цену за единицу для всех проданных продуктов.
Получите продукты с прейскурантной ценой 100 или более, которые были проданы менее чем за 100.
Получите идентификатор продукта, имя и прейскурантную цену для каждого продукта, у которого прейскурантная цена равна 100 или более, а продукт был продан менее чем за 100.
Решение (представленное по той же ссылке выше) для вышеуказанной проблемы: они представили следующий запрос, который возвращает 7 записей:
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ProductID IN (SELECT ProductID
FROM SalesLT.SalesOrderDetail
WHERE UnitPrice < 100.00)
AND ListPrice >= 100.00
ORDER BY ProductID;
Но при тех же критериях поиска мой собственный следующий запрос возвращает 27 записей (показано ниже), где list prices >=100 и UnitPrice < 100:
Вопрос: Почему решение, предложенное Microsoft Lab, правильное, а то, что делаю я, неверно:
SELECT p.ProductID, p.Name, p.ListPrice, s.UnitPrice
FROM SalesLT.Product p
JOIN SalesLT.SalesOrderDetail s ON p.ProductID = s.ProductID
WHERE p.ListPrice >= 100.00
AND s.UnitPrice < 100.00
ORDER BY ProductID;
Ответы 1
Для начала давайте будем немного педантичны и учтем разницу между тем, что от вас просила задача, и тем, что вы сделали вместо этого. В задаче вам предлагалось использовать подвыборку с основным запросом выбора SalesLT.Product и запросом подвыборки SalesLT.SalesOrderDetail. Вместо того, чтобы выполнить задание, вы сделали join. Технически вы также можете достичь цели задачи с помощью join, но вам нужно следовать инструкциям и, если вас просят использовать подзапрос, сделайте это.
Во-вторых, логическая ошибка, которую вы допустили, заключается в том, что когда вы присоединяетесь SalesLt.Product к SalesLT.SalesOrderDetail, вы присоединяетесь ко всем совпадениям, поэтому если у вас есть товар, который был продан 8 раз, то вы получите 8 пар и, следовательно, у вас будет 8 записей, соответствующих этот продукт вместо 1 (см., например, ProductID 876), который был бы результатом, если бы вы следовали инструкции и использовали подзапрос. Но чтобы добиться того же с помощью join, вы можете использовать group by, то есть объединить несколько кортежей товаров и продаж в отдельные записи, ровно по одной записи для каждого продукта, имеющего одно совпадение:
SELECT p.ProductID, p.Name, p.ListPrice
FROM SalesLT.Product p
JOIN SalesLT.SalesOrderDetail s ON p.ProductID = s.ProductID
WHERE p.ListPrice >= 100.00
AND s.UnitPrice < 100.00
GROUP BY p.ProductID, p.Name, p.ListPrice
ORDER BY ProductID;
В-третьих, ваш join и его подвыбор не оптимальны, поскольку они будут искать все пары, а не один пример. EXISTS ищет одно совпадение в подвыборке и, если совпадение найдено, прекращает поиск нового, тогда как IN находит ВСЕ совпадения и, следовательно, занимает больше времени, и в нашем случае нас интересует только EXISTS, поэтому:
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE EXISTS (SELECT SalesLT.SalesOrderDetail.ProductID
FROM SalesLT.SalesOrderDetail
WHERE UnitPrice < 100.00 AND SalesLT.SalesOrderDetail.ProductID = SalesLT.Product.ProductID)
AND ListPrice >= 100.00
ORDER BY ProductID;
В-четвертых, первая задача не обсуждалась. Это будет выглядеть примерно так же, как их подвыбор:
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice > (SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail
WHERE SalesLT.Product.ProductID = SalesLT.SalesOrderDetail.ProductID)
ORDER BY ProductID;
Хорошее объяснение - конкретно по IN Vs. СУЩЕСТВУЕТ. Я кое-чему научился.
@nam рад помочь!
в командном запросе легко уменьшить количество продуктов, а затем уменьшить их на ListPrice, ваши запросы должны добавить условия в предложение on.