我正在尝试从 3 个表构建查询。 销售、商店和商品。 目标是获得最畅销的 10 种产品,并为每个产品显示销售额最低的商店。 我有以下查询,但它没有得到最重要的产品,因为它也提到了store_desc。目标是在没有商店描述的情况下获得销售额最高的前 10 种产品,然后获得最低的"A",即数量
select distinct top 10 sum(net_sale) as sales, sum(quantity)
as a, sales.item_code,stores.store_desc from sales join
stores on stores.store_code=sales.store_code group by
sales.net_sale, sales.item_code,stores.store_desc order by
sales desc
您可以使用以下方法获取每个商店每种产品的销售额:
select s.store_code, s.item_code, sum(i.quantity) as qty
from sales s
group by s.store_code, s.item_code;
通过增加一项,我们还获得了每个商店的总数量:
select s.store_code, s.item_code, sum(i.quantity) as qty,
sum(sum(i.quantity)) over (partition by s.item_code) as item_qty
from sales s
group by s.store_code, s.item_code;
然后,以此作为子查询,我们可以枚举它们:
select s.*,
dense_rank() over (order by item_qty desc) as item_rank,
row_number() over (partition by item_code order by qty asc) as item_store_rank
from (select s.store_code, s.item_code, sum(i.quantity) as qty,
sum(sum(i.quantity)) over (partition by s.item_code) as item_qty
from sales s
group by s.store_code, s.item_code
) s
然后将其设为子查询。 你想要:
where item_rank <= 10 and item_store_rank <= 3
注意: 这不包括不销售某些商品的商店。 如果这是一个问题,我建议你提出一个新问题,澄清如何处理此类案件。