为什么计算顺序改变

Question

为什么这个查询错误地计算了ordertaken的列?是关于分组吗?

SELECT t2.employeeid, t1.firstname || ' ' || t1.lastname as fullname,
sum(t3.quantity*t4.price) as totalsales,
COUNT(t2.orderid) AS ordertaken, COUNT(DISTINCT t2.customerid) AS uniquecustomercount   
from  employees as t1
join orders as t2 on t2.employeeid = t1.employeeid
join order_details as t3 on t2.orderid = t3.orderid
join products as t4 on t4.productid = t3.productid 
group by t2.employeeid, fullname

正确计算列ordertaken

Answer 1

如果我理解正确的话，您需要对两个计数使用不同的计数:

SELECT
t2.employeeid,
t1.firstname || ' ' || t1.lastname AS fullname,
SUM(t3.quantity*t4.price) AS totalsales,
COUNT(DISTINCT t2.orderid) AS ordertaken,
COUNT(DISTINCT t2.customerid) AS uniquecustomercount
FROM employees AS t1
INNER JOIN orders AS t2 ON t2.employeeid = t1.employeeid
INNER JOIN order_details AS t3 ON t2.orderid = t3.orderid
INNER JOIN products AS t4 ON t4.productid = t3.productid 
GROUP BY t2.employeeid, fullname;

对orders表的计数需要不同的计数，因为到order_details和products的连接可能会重复给定的订单记录，从而放大计数。