mysql - sql 查询获取按销售额分组的第三高销售额

Question

从MySQL表中获取第三高的销售额(按销售额分组)

 select Top 3 * from t1 group by sale_Amnt 

|Id|product_name|product_group    |sale_Amnt(INR)
------------------------------------------------
 1|  p1       |   Cosmetic       |4485
 2|  p2       |   Cosmetic       |8525
 3|  p3       |   Health         |12589
 4|  p4       |   Health         |8525
 5|  p5       |   Home Appliances|9858
 6|  p6       |   Home Appliances|12589

预期输出

|Id|product_name|product_group    |sale_Amnt(INR)
------------------------------------------------
 2|  p2       |   Cosmetic       |8525
 4|  p4       |   Health         |8525`

Answer 1

提议的重复项是对问题的误解。该问题似乎正在寻找总体上第三高的值，但考虑到重复项。

您可以在 SQL Server 中使用 offset/fetch 获取第三行:

select t.*
from t
where t.sale_amount = (select t2.sale_amount
                       from t t2
                       group by t2.sale_amount
                       order by t2.sale_amount desc
                       offset 2 fetch first 1 row only
                      );

在 MySQL 中，这将是:

select t.*
from t
where t.sale_amount = (select t2.sale_amount
                       from t t2
                       group by t2.sale_amount
                       order by t2.sale_amount desc
                       limit 1 offset 2
                      );