基于折扣的五大产品及展厅查询

Question

我有一张桌子l_sales，它包含Product_id、陈列室、折扣栏.I只想要基于折扣的前5家展厅，而一家展厅可以销售多种产品，我想要前5家展厅的前5种产品都是基于折扣的。如何做到这一点？Showroom|Product_id|Discount A.肥皂。90 A. Pen.60 B. Pen。70 C.铅笔。40 D.蛋糕。60 E.巧克力。50

结果将是Showroom|Product_id|Discount A. Soap。90 A. Pen.60 B. Pen。70 C.铅笔。40 D.蛋糕。我们在这里买两次A，因为它卖两种产品，而且折扣一定比其他产品大

Answer 1

您可以如下所示使用分析功能：

SELECT SHOWROOM, Product_id, DISCOUNT 
  FROM (SELECT T.*, 
               ROW_NUMBER() OVER (PARTITION BY SHOWROOM ORDER BY DISCOUNT DESC) AS RN 
          FROM (SELECT T.*, 
                       DENSE_RANK() OVER (ORDER BY S DESC) DR 
                  FROM (SELECT T.*,
                               SUM(DISCOUNT) OVER (PARTITION BY showroom) S
                          FROM l_sales T) T) 
          WHERE DR <= 5)
 WHERE RN <= 5