mysql限制id_MySQL-如何限制每个ID一个结果？

I have the following query which creates a view table showing the highest salesperson in a store with few other details:

CREATE OR REPLACE VIEW sales_data AS

SELECT s.storename AS "Store",

e.employee_name AS "Employee",

e1.employee_name AS "Manager",

SUM(p.total_sale_value) AS "Sales Value"

FROM fss_Shop s

JOIN Employee e ON e.storeid = s.storeid

JOIN Payment p ON p.employee_number = e.employee_number

JOIN Employee e1 ON e1.employee_number = e.manager_number

WHERE s.storeid=1

GROUP BY e.employee_name

ORDER BY SUM(p.total_sale_value) DESC LIMIT 1;

The above query will only show the sales data for a single store and the reason being as I have stated WHERE s.storeid=1. I have 20 stores in my table. How can I change the above query so that it gives me sales data for 20 stores (so that's 20 rows).

解决方案CREATE OR REPLACE VIEW employee_sales_totals AS

SELECT

e.*,

SUM(p.total_sale_value) AS total_sale_value

FROM

Employee e

INNER JOIN

Payment p

ON p.employee_number = e.employee_number

GROUP BY

e.id -- This should be the Primary Key / Surrogate Key of the employee table

;

CREATE OR REPLACE VIEW shop_top_employee_by_sales_value AS

SELECT

s.storename AS "Store",

e.employee_name AS "Employee",

m.employee_name AS "Manager",

p.total_sale_value AS "Sales Value"

FROM

(

SELECT storeid, MAX(total_sale_value) AS total_sale_value

FROM employee_sales_totals

GROUP BY storeid

)

p

INNER JOIN

employee_sales_totals e

ON e.storeid = p.storeid

AND e.total_sale_value = p.total_sale_value

INNER JOIN

fss_Shop s

ON s.storeid = e.storeid

INNER JOIN

Employee m

ON m.employee_number = e.manager_number

;

As per the answer to your previous question, if multiple employees are tied for the same total sales amount in the same store, all such employees would be returned.