与 Count 和 SQl Server 2005 不同 [英] Distinct with Count and 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

这篇关于与 Count 和 SQl Server 2005 不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！