与Count和SQl Server 2005不同 [英] Distinct with Count and SQl Server 2005

问题描述

尝试处理一个查询，返回前三名销售的产品，三个具有不同的艺术家。

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

推荐答案

p>

Use this:

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名），你必须修改查询，所以前三个报告将是这样：

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屋！