SQL选择每组WITH CONDITION的前3个值 [英] SQL-select top 3 values per group WITH CONDITION

问题描述

我想针对每个标签分别列出最畅销的3种产品，分别列出不同的产品类别. 数据如下:

I want to pull out top 3 selling products for different product category per tag. Data looks like this:

tag  | product_name | product_category | order_count
tag1 | product1     | category1        | 100
tag1 | product2     | category2        | 80
tag1 | product3     | category2        | 60
tag1 | product4     | category3        | 50
......

我知道如何使用ROW_NUMBER()提取每个标签中销售量最高的3种产品，但是它将返回product1，product2，product3.我不想要product3，因为它与product2属于同一类别.我要代替product4.如何在SQL Server中执行此操作?

I know how to pull out top 3 selling products per tag using ROW_NUMBER(), but it will return product1,product2,product3. I don't want product3 because it belongs to the same category as product2. I want product4 instead. How to do this in SQL server?

推荐答案

第一个ROW_NUMBER删除每个标签和product_category的重复行，第二个ROW_NUMBER选择每个标签的前3个畅销产品

First ROW_NUMBER removes duplicate rows per tag and product_category, second ROW_NUMBER selects top 3 selling products per tag

;WITH cte AS
 (SELECT *, ROW_NUMBER() OVER(PARTITION BY tag, product_category ORDER BY order_count DESC) AS rn
  FROM yourtable
  ), cte2 AS
  (SELECT *, ROW_NUMBER() OVER(PARTITION BY tag ORDER BY order_count DESC) AS rn2
   FROM cte
   WHERE rn = 1
   )
   SELECT *
   FROM cte2
   WHERE rn2 <= 3

在SQLFiddle

下一个使用派生表

;WITH cte AS
 (SELECT t2.tag, t2.product_name, t2.product_category, t2.order_count,
         ROW_NUMBER() OVER(PARTITION BY t2.tag ORDER BY order_count DESC) AS rn
  FROM (SELECT tag, product_category, MAX(order_count) AS maxCount
        FROM yourtable
        GROUP BY tag, product_category
        ) t1 JOIN yourtable t2 ON t1.tag = t2.tag 
          AND t1.product_category = t2.product_category
          AND maxCount = order_count
  )
  SELECT *
  FROM cte
  WHERE rn <= 3

在 SQLFiddle

这篇关于SQL选择每组WITH CONDITION的前3个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！