Help: mysql query for various products sales record sales information, most all product sales record-CodePudding

Help: mysql query for various products sales record sales information, most all tied for first product sales record will be listed
Product_name salesman salesman_id
Product_001 mark c210001
Product_001 Tom c210002
Product_001 lily c210003
Product_001 alex c210004
Product_001 mark c210001
Product_001 mark c210001
Product_001 mark c210001
Product_001 Tom c210002
Product_001 Tom c210002
Product_001 Tom c210002
Product_001 alex c210004
Product_002 mark c210001
Product_002 mark c210001
Product_002 mark c210001
Product_002 Tom c210002
Product_002 Tom c210002
Product_002 Tom c210002
Product_002 Tom c210002
Product_002 lily c210003
Product_002 lily c210003
Product_002 lily c210003
Product_002 lily c210003
Product_002 lily c210003
Product_002 lily c210003
Product_002 lily c210003
Product_002 alex c210004
Product_002 alex c210004
Product_002 alex c210004
Product_002 alex c210004

CodePudding user response:

 select product_name salesman_id, 
DENSE_RANK () OVER (PARTITION BY product_name ORDER BY DESC) CNT AS fairly rk, window function is used to collect sales ranking 
The from (select product_name salesman_id, COUNT (1) the AS CNT 
From the sales 
Group by product_name, salesman_id) AS t - statistic every salesman sales record for each type of product 
Where fairly rk=1 - the first choice, including tied for first

If you want to learn SQL, you can refer to the ,