我有以下查询,它创建了一个视图表,其中显示了商店中最高的销售人员,但没有其他详细信息:

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行)的销售数据。

最佳答案

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
;

根据你之前问题的答案,如果同一家商店的同一销售总额中有多个员工被捆绑在一起,那么所有这些员工都将被退回。

关于mysql - MySQL-如何限制每个ID一个结果?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/47533531/

10-10 18:29
查看更多