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

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

方法1：

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：

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

查询结果：