Intereting Posts
BizTalk WCF-SQL Adapter Выбор из представления Получение следующей записи из базы данных Как загрузить изображение в SQL с MVC3 в одной форме с помощью ADO.NET? Удалить из таблицы, если идентификатор не существует в другой таблице Выберите из другой таблицы, если значение существует Объединение двух или более столбцов из разных SQL-запросов Быстрый способ запроса последней записи? Как добавить вычисляемый столбец в Access через SQL Запрос MySQL для извлечения данных с именами столбцов Fire SQL Trigger только тогда, когда конкретный пользователь обновляет строку Пароль SQL Server 2008, заканчивающийся точкой с запятой Удаление заполненных нулей в шестнадцатеричном столбце Сгенерировать сценарий как для схемы, так и для данных Выделить все точки геопространства внутри ограничивающей рамки DELETE FROM `table` AS` alias` … WHERE `alias`.`column` … почему синтаксическая ошибка?

SQL – подзапрос в агрегированной функции

Я использую базу данных Northwind для обновления моих навыков SQL, создавая несколько более или менее сложных запросов. К сожалению, я не смог найти решение для моего последнего варианта использования: «Получите сумму пяти наивысших заказов для каждой категории в 1997 году».

В число задействованных таблиц входят:

Orders(OrderId, OrderDate) Order Details(OrderId, ProductId, Quantity, UnitPrice) Products(ProductId, CategoryId) Categories(CategoryId, CategoryName) 

Я попробовал следующий запрос

 SELECT c.CategoryName, SUM( (SELECT TOP 5 od2.UnitPrice*od2.Quantity FROM [Order Details] od2, Products p2 WHERE od2.ProductID = p2.ProductID AND c.CategoryID = p2.CategoryID ORDER BY 1 DESC)) FROM [Order Details] od, Products p, Categories c, Orders o WHERE od.ProductID = p. ProductID AND p.CategoryID = c.CategoryID AND od.OrderID = o.OrderID AND YEAR(o.OrderDate) = 1997 GROUP BY c.CategoryName 

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

Подзапросы обычно не разрешены в совокупных функциях. Вместо этого переместите агрегат внутри подзапроса. В этом случае вам потребуется дополнительный уровень подзапроса из-за top 5 :

 SELECT c.CategoryName, (select sum(val) from (SELECT TOP 5 od2.UnitPrice*od2.Quantity as val FROM [Order Details] od2, Products p2 WHERE od2.ProductID = p2.ProductID AND c.CategoryID = p2.CategoryID ORDER BY 1 DESC ) t ) FROM [Order Details] od, Products p, Categories c, Orders o WHERE od.ProductID = p. ProductID AND p.CategoryID = c.CategoryID AND od.OrderID = o.OrderID AND YEAR(o.OrderDate) = 1997 GROUP BY c.CategoryName, c.CategoryId 

Используйте CTE с функцией ранжирования ROW_NUMBER вместо избыточного подзапроса.

  ;WITH cte AS ( SELECT c.CategoryName, od2.UnitPrice, od2.Quantity, ROW_NUMBER() OVER(PARTITION BY c.CategoryName ORDER BY od2.UnitPrice * od2.Quantity DESC) AS rn FROM [Order Details] od JOIN Products p ON od.ProductID = p.ProductID JOIN Categories c ON p.CategoryID = c.CategoryID JOIN Orders o ON od.OrderID = o.OrderID WHERE o.OrderDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, '19970101'), 0) AND o.OrderDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, '19970101')+1, 0) ) SELECT CategoryName, SUM(UnitPrice * Quantity) AS val FROM cte WHERE rn < 6 GROUP BY CategoryName 

Это определенно проблема с дополнительными запросами здесь – отличная статья об этом (первоначально написанная для Access, но синтаксис идентичен), а также orderdate = 1997 дадут дату заказа на 1 января 1997 года – вам нужно datepart (year, orderdate) = 1997 , как только у вас есть (до пяти) строк, возвращаемых для каждой категории, вы можете инкапсулировать возвращаемые строки и агрегировать их

Я столкнулся с очень похожей проблемой с подзапросом Access, где записи были отсортированы по дате. Когда я использовал функцию «Last» aggregate, я обнаружил, что она прошла через все подзапросы и извлекла последнюю строку данных из таблицы Access, а не отсортированный запрос, как предполагалось. Хотя я мог бы переписать запрос на использование агрегированной функции в первом наборе скобок (как было предложено ранее), мне было проще сохранять результаты запроса в виде таблицы в базе данных, отсортированной в том порядке, который я хотел, а затем использовать «Последний «агрегатная функция для получения значений, которые я хотел. Я буду запускать запрос обновления в будущем, чтобы сохранить текущие результаты. Неэффективен, но эффективен.