与 Count 和 SQl Server 2005 不同
尝试处理一个查询，该查询将返回最畅销的 3 个产品，其中三个具有不同的艺术家.我一直在寻找独特的艺术家.
Trying to work on a query that will return the top 3 selling products with the three having a distinct artist. Im getting stuck on getting the unique artist.
简化表架构
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
推荐答案
使用这个:
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
但是您不能停留在那个查询上，如果一位艺术家的两种产品与最高销量有关呢?数据是这样的……
But you cannot stop at that query, how about if there are two products on one artist that are ties on highest selling? This is how the data like this...
beatles yesterday 1000
beatles something 1000
elvis jailbreak rock 800
nirvana lithium 600
tomjones sexbomb 400
...将导致以下使用上述查询:
...will result to following using the above query:
beatles yesterday 1000
beatles something 1000
elvis jailbreak rock 800
选择哪一个?昨天还是什么?由于您不能随意选择其中之一，因此您必须同时列出两者.另外，如果销量最高的前 10 名属于甲壳虫乐队并且是领带，每件的数量为 1000 件呢?由于这是您要避免的最好的事情(即报告前 3 名中的同一位艺术家)，您必须修改查询以使前 3 名报告如下所示:
Which one to choose? yesterday or something? Since you cannot arbitrarily chose one over the other, you must list both. Also, what if the top 10 highest selling belongs to beatles and are ties, each with a quantity of 1000? Since that is the very best thing you are avoiding(i.e. reporting same artist on top 3), you have to amend the query so the top 3 report will look like this:
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 个数量的产品怎么样?上面的查询仍然有效吗?是的，它仍然会起作用.由于 top_3 CTE 只关注每个艺术家已经过滤的前几名.所以这个源数据...
How about if the beatles has another product which has 900 qty? Will the above query still work? Yes, it will still work. Since the top_3 CTE only concerns itself from the already filtered top qty on each artist. So this source data...
beatles yesterday 1000
beatles something 1000
beatles and i love her 900
elvis jailbreak rock 800
nirvana lithium 600
tomjones sexbomb 400
...仍会导致以下结果:
...will still result to following:
beatles yesterday 1000
beatles something 1000
elvis jailbreak rock 800
nirvana lithium 600
相关文章