我有以下查询,它创建一个视图表,显示商店中最高的销售人员以及其他一些详细信息:
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
;
根据您上一个问题的答案,如果同一家商店中有多名员工获得相同的总销售额,则所有这些员工都将被退回。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)