mysql实现over partition by 的查询(分组排序求TOP)

需求是求以下表中每一种分类销量最多的三种商品：

 

方法1：

?

1 2 3 4 5 6

SELECT TBL.ID,TBL.PRODUCTNAME,TBL.TYPENAME,TBL.SALECOUNT FROM PRODUCT TBL LEFT JOIN PRODUCT L_TBL ON TBL.TYPENAME = L_TBL.TYPENAME AND TBL.SALECOUNT< L_TBL.SALECOUNT GROUP BY TBL.ID,TBL.PRODUCTNAME,TBL.TYPENAME,TBL.SALECOUNT HAVING COUNT(L_TBL.ID)< 3 ORDER BY TBL.TYPENAME,TBL.SALECOUNT DESC

方法2：

?

1 2 3 4

SELECT TBL.ID,TBL.PRODUCTNAME,TBL.TYPENAME,TBL.SALECOUNT FROM PRODUCT TBL WHERE 3>(SELECT COUNT(*) FROM PRODUCT WHERE TYPENAME =TBL.TYPENAME AND SALECOUNT>TBL.SALECOUNT) ORDER BY TBL.TYPENAME,TBL.SALECOUNT DESC

 

查询结果：