显示输出(包括没有销售的区域(时遇到问题。
保险公司保存其员工的销售记录。每个员工都被分配到一个州。各州按区域分组。下表包含数据：
TABLE regions
id INTEGER PRIMARY KEY
name VARCHAR(50) NOT NULL
TABLE states
id INTEGER PRIMARY KEY
name VARCHAR(50) NOT NULL
regionId INTEGER NOT NULL REFERENCES regions(id)
TABLE employees
id INTEGER PRIMARY KEY
name VARCHAR(50) NOT NULL
stateId INTEGER NOT NULL REFERENCES states(id)
TABLE sales
id INTEGER PRIMARY KEY
amount INTEGER NOT NULL
employeeId INTEGER NOT NULL REFERENCES employees(id)
管理层需要一份比较区域销售分析报告。
编写一个返回以下内容的查询：
The region name.
Average sales per employee for the region (Average sales = Total sales made for the region / Number of employees in the region).
The difference between the average sales of the region with the highest average sales, and the average sales per employee for the region (average sales to be calculated as explained above).
没有销售的区域也应返回。在计算第 2 列和第 3 列时，使用 0 表示此类区域的每位员工的平均销售额。
这是到目前为止的代码：
SELECT regions.name,
(CASE WHEN sales.amount<>0 THEN SUM(sales.amount)/COUNT(employees.id)ELSE 0 end)
AS average_sale,
(CASE WHEN sales.amount<>0 THEN SUM(sales.amount)/COUNT(employees.id)-MAX(sales.amount) ELSE 0 end)
AS Diff
FROM regions
JOIN states
ON states.regionId = regions.Id
JOIN employees
ON states.id=employees.stateId
JOIN sales
ON employees.id=sales.employeeId
GROUP BY regions.Id;
如果你从这里到达这篇文章，我们需要有一个查询来传递所有边缘情况。最重要的是："员工可以有多个销售。
该区域每位员工的平均销售额(平均销售额 = 该区域的总销售额/该区域的员工人数(。
请记住，我们每个员工都会完成多项销售。 因此，我们需要准确计算该地区的员工人数。员工的不同数量将为我们提供正确的值，每位员工的平均销售额也将是准确的。
左联接，以便我们获得某些员工销售额的 NULL 值，这为我们提供了没有销售额的区域，该区域将显示为零。
所以让我们试一试：
with SalesAvg as (
select R.name as rgn,
CASE WHEN SUM(IFNULL(SL.amount,0)) = 0 THEN 0 /*region with no sales returning 0*/
ELSE SUM(IFNULL(SL.amount,0)) / COUNT(DISTINCT E.id) END as average
/*distinct employee count gives the correct value for number of employees in ther region.*/
from regions R
left join states S on R.id = S.regionId
left join employees E on S.id = E.stateId
left join sales SL on E.id = SL.employeeId
group by R.Id, R.name
)
select
rgn,
average,
(select max(average) from SalesAvg)- average as difference /*highest average sales -region average*/
from SalesAvg
group by rgn
with c as (
select sum(sales.amount)/count(employee.id) as average from regions region1
left join states state on region1.id = state.regionId
left join employees employee on state.id = employee.stateId
left join sales sales on sales.employeeId = employee.id
group by region1.id)
select region.name,
(case when sum(sales.amount)<>0 then sum(sales.amount)/count(employee.id) else 0 end)
as average,
(case when sum(sales.amount)<>0 then (select max(average) from c) -
(sum(sales.amount)/count(employee.id))
else (select max(average) from c) end)
as difference
from regions region
left join states state on region.id = state.regionId
left join employees employee on state.id = employee.stateId
left join sales sales on sales.employeeId = employee.id
group by region.id
select regions.name, avg(coalesce(sales.amount,0)) as average
from regions
left join states
on regions.id=states.regionId
left join employees
on states.id=employees.stateId
left join sales
on employees.id=sales.employeeid
group by (regions.name)
order by avg(sales.amount) desc
SELECT r.name,
COALESCE(SUM(COALESCE(s.amount, 0)), 0)/COALESCE(COUNT(e.Name), 0) AS avg_amount,
ABS(COALESCE(SUM(COALESCE(s.amount, 0)), 0)/COALESCE(COUNT(e.Name), 0) -
(SELECT MAX(amount)
FROM
(SELECT COALESCE(SUM(s.amount), 0)/COALESCE(COUNT(e.Name), 0) AS amount
FROM sales s
JOIN employees e
ON s.employeeid = e.Id
JOIN states st
ON st.id = e.stateid
JOIN regions r
ON r.id = st.regionid
GROUP BY r.name))) AS diff
FROM sales s
JOIN employees e
ON s.employeeid = e.Id
JOIN states st
ON st.id = e.stateid
JOIN regions r
ON r.id = st.regionid
GROUP BY r.name
WITH c AS (
SELECT regions.name AS name,
(CASE WHEN sales.amount <> 0
THEN SUM(sales.amount) / COUNT(employees.id)
ELSE 0 END) AS average
FROM regions
LEFT JOIN states ON regions.id = states.regionid
LEFT JOIN employees ON states.id = employees.stateId
LEFT JOIN sales ON employees.id = sales.employeeid
GROUP BY regions.id)
SELECT name,
average,
(SELECT MAX(average) FROM c) - average as difference
FROM c
GROUP BY name
with D as(
select regions.name as name,
case when sum(coalesce(sales.amount,0)) = 0 then 0
else sum(coalesce(sales.amount,0))/count( distinct employees.id)
以平均值结束 从地区 左联接状态 on regions.id=states.regionId 左加入员工 on states.id=employees.stateId 左加入销售 on employees.id=sales.employeeid 分组依据 (regions.name( 按平均(销售额(订购( 选择名称， 平均 (从 D 中选择最大值(平均值(-平均值作为差值 从D;
with sales_per_region as
(
select regions.name , COALESCE(sum(amount)/count(employees.id),0) as average
from regions
left join states on regions.id = states.regionId
left join employees on states.id = employees.stateId
left join sales on employees.id = sales.employeeId
group by 1
),
max_sales_per_region as
(
select max(average) as max_average_sales
from sales_per_region
)
select name, average, max_average_sales-average as difference
from sales_per_region,max_sales_per_region
我试图解决这个问题，但被困了很长时间。联系了其中一位专家，并得到了使用不同员工计数的建议。这改变了游戏规则。
select region_name as name,avg_sales as average ,(max_sales-avg_sales ) as difference from
(
with
cte as (
select r.name as region_name,
coalesce((sum(ss.amount)/count(distinct e.id)),0) as avg_sales
from regions r
left join states s on r.id=s.regionId
left join employees e on s.id=e.stateId
left join sales ss on e.id=ss.employeeId
group by r.name
)
select region_name,avg_sales,
case
when avg_sales>=0 then (select max(avg_sales) from cte) end as max_sales
from cte
)x;
在 mysql 中尝试这种方式
with sales_avg AS (
select r.name as region,
case when sum(ifnull(s.amount,0)) = 0 then 0
else sum(ifnull(s.amount,0))/count(distinct e.id) end as average
from regions r
left join states st on r.id = st.regionid
left join employees e on st.id = e.stateid
left join sales s on e.id = s.employeeid
group by r.id, r.name)
select region, average, (select max(average) from sales_avg) - average as difference
from sales_avg
这在MS SQL中对我有用：
WITH sales_avg AS (
SELECT
r.name AS region,
CASE
WHEN SUM(ISNULL(s.amount,0)) = 0 THEN
0
ELSE
SUM(ISNULL(s.amount,0))/COUNT(distinct e.id)
END AS average
FROM regions r
LEFT JOIN states st
ON r.id = st.regionid
LEFT JOIN employees e
ON st.id = e.stateid
LEFT JOIN sales s
ON e.id = s.employeeid
GROUP BY r.id, r.name)
SELECT
region,
average,
(
(SELECT MAX(average) FROM sales_avg) - average
) AS difference
FROM sales_avg
GROUP BY region, average