MySQL-如何限制每个ID一个结果? [英] MySQL - How to limit one result per 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; 

上面的查询将仅显示单个商店的销售数据，其原因正如我所说的WHERE s.storeid=1.我的桌子上有20家商店.如何更改上述查询，以便为我提供20家商店(即20行)的销售数据.

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.

这篇关于MySQL-如何限制每个ID一个结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！