Intereting Posts

Различия с Count и SQl Server 2005

Попытка работать над запросом, который вернет топ-3 продаваемых продуктов, у трех из которых будет отличный художник. Я застрял в получении уникального художника.

Упрощенная схема таблиц

Product ProductID Product Name Artist Name OrderItem ProductID Qty So results would look like this... PID artist qty 34432, 'Jimi Hendrix', 6543 54833, 'stevie ray vaughan' 2344 12344, 'carrie underwood', 1 

Solutions Collecting From Web of "Различия с Count и SQl Server 2005"

Использовать это:

 with summed_sales_of_each_product as ( select p.artist_name, p.product_id, sum(i.qty) as total from product p join order_item i on i.product_id = p.product_id group by p.artist_name, p.product_id ), each_artist_top_selling_product as ( select x_in.artist_name, x_in.product_id, x_in.total from summed_sales_of_each_product x_in where total = (select max(x_out.total) from summed_sales_of_each_product x_out where x_out.artist_name = x_in.artist_name) ) select top 3 artist_name, product_id, total from each_artist_top_selling_product order by total desc 

Но вы не можете остановиться на этом вопросе, как насчет того, есть ли два продукта на одном художнике, которые связаны с самыми высокими продажами? Вот как такие данные …

 beatles yesterday 1000 beatles something 1000 elvis jailbreak rock 800 nirvana lithium 600 tomjones sexbomb 400 

… приведет к следующему использованию вышеуказанного запроса:

 beatles yesterday 1000 beatles something 1000 elvis jailbreak rock 800 

Какой из них выбрать? вчера или что-то еще? Поскольку вы не можете произвольно выбирать один над другим, вы должны указать оба. Кроме того, что, если топ-10 самых продаваемых принадлежит битлам и являются галстуками, каждый из которых имеет количество 1000? Поскольку это самое лучшее, чего вы избегаете (т. Е. Сообщать о том же художнике в верхней части 3), вам нужно внести поправку в запрос, так что отчет 3 будет выглядеть следующим образом:

 beatles yesterday 1000 beatles something 1000 elvis jailbreak rock 800 nirvana lithium 600 

Внести изменения в:

 with summed_sales_of_each_product as ( select p.artist_name, p.product_id, sum(i.qty) as total from product p join order_item i on i.product_id = p.product_id group by p.artist_name, p.product_id ), each_artist_top_selling_product as ( select x_in.artist_name, x_in.product_id, x_in.total from summed_sales_of_each_product x_in where x_in.total = (select max(x_out.total) from summed_sales_of_each_product x_out where x_out.artist_name = x_in.artist_name) ), top_3_total as ( select distinct top 3 total from each_artist_top_selling_product order by total desc ) select artist_name, product_id, total from each_artist_top_selling_product where total in (select total from top_3_total) order by total desc 

Как насчет того, есть ли у битлов другой продукт, который имеет 900 qty? Будет ли выполняться вышеуказанный запрос? Да, он все равно будет работать. Поскольку top_3 CTE относится только к уже отфильтрованной вершине qty для каждого исполнителя. Итак, эти исходные данные …

 beatles yesterday 1000 beatles something 1000 beatles and i love her 900 elvis jailbreak rock 800 nirvana lithium 600 tomjones sexbomb 400 

… все равно приведет к следующему:

 beatles yesterday 1000 beatles something 1000 elvis jailbreak rock 800 nirvana lithium 600 

Если я правильно понял вашу схему, вы должны сделать это следующим образом:

 select top 3 * from( select p.ProductId, p.ArtistName, sum(o.qty) as qty from Product p, OrderItem o where p.ProductId = o.ProductId group by p.productId, p.ArtistName order by sum(o.qty) ) 

Я не знаю, что вы хотите сделать, если у Artist есть два топ-рейтинга продуктов с идентичными продажами – это вернет два в случае галстука.

Если вы хотите добавить другие критерии, например «самые последние», вы должны добавить это для обоих подзапросов.

 select top 3 sales_by_item.ProductID, sales_by_item.Artist, sales_by_item.Qty from ( select * from product x inner join OrderItem y on x.productid = y.productid group by productid, Artist ) sales_by_item inner join ( select artist, max(qty) as maxqty from product x inner join OrderItem y on x.productid = y.productid group by artist ) max_by_artist on sales_by_item.artist = max_by_artist.artist and sales_by_item.qty = max_by_artist.maxqty order by sales_by_item.qty 

Отредактировано, чтобы сделать имена подзапросов более наглядными

Вторая попытка. Я не в состоянии проверить этот код, и я не уверен, что у меня это условие «раздел by» настроено правильно. Идея такова:

  • Внутренний запрос получает сумму Qty для всех товаров / исполнителей и использует функцию row_number () для их номера, начиная с самого большого, и сбрасывает порядок для каждого исполнителя. (Это можно сделать, но мой синтаксис может быть отключен.)
  • Внешний запрос выбирает первый (самый большой) элемент для каждого исполнителя и возвращает только первые три (ordere by Qty)
  • Если артисты топ-два продукта связаны для общего количества, я произвольно нарушаю галстук в пользу «самого раннего» альбома.

(Я стараюсь избегать использования «Top n», но уже поздно, и я не хочу решать другую функцию row_number ().)

 SELECT top 3 ProductId ,ArtistName ,Qty from (-- Products + Artists by total qty select pr.ProductId ,pr.ArtistName ,sum(oi.Qty) Qty ,row_number() over (partition by pr.ArtistName order by pr.ArtistName, sum(oi.Qty) desc, pr.ProductId) Ranking from Product pr inner join OrderItem oi on oi.ProductID = pr.ProductID group by pr.ProductId, pr.ArtistName) BestSellers where Ranking = 1 group by ProductId, ArtistName) BestArtists order by Qty desc - SELECT top 3 ProductId ,ArtistName ,Qty from (-- Products + Artists by total qty select pr.ProductId ,pr.ArtistName ,sum(oi.Qty) Qty ,row_number() over (partition by pr.ArtistName order by pr.ArtistName, sum(oi.Qty) desc, pr.ProductId) Ranking from Product pr inner join OrderItem oi on oi.ProductID = pr.ProductID group by pr.ProductId, pr.ArtistName) BestSellers where Ranking = 1 group by ProductId, ArtistName) BestArtists order by Qty desc 

Анализируя ваш запрос, кажется, что результаты должны быть наивысшим количеством для трех лучших художников. Итак, если у Jimi Hendrix есть 10 лучших продуктов, а Стиви Рэй Воган – 11-й, вы хотите, чтобы Jimi с его самым высоким продуктом, а затем Стиви с его самым высоким продуктом.

 With ProductRanksForArtists As ( Select P.ProductId, P.ArtistName, Sum(O.Qty) As Total , ROW_NUMBER OVER( PARTITION BY P.ArtistName ORDER BY Sum(O.Qty) DESC ) As ProductRank From Product As P Join OrderItem As O On O.ProductId = P.ProductId Group By P.ProductId, P.ArtistName ) , HighestProductForArtists As ( Select ProductId, ArtistName, Total , ROW_NUMBER OVER( ORDER BY Total DESC ) As TotalRank From ProductRanksForArtists Where ProductRank = 1 ) Select ProductId, ArtistName, Total From HighestProductForArtists Where TotalRank <= 3 

Попробуй это

Выберите топ-3 исполнителя, счетчик (художник) из группы имен табуляций по заказу художника по исполнителю count (artist) desc