时间戳

首页 > 解决方案 > 为什么加入表后初始的 COUNT 值会发生变化？

sql - 为什么加入表后初始的 COUNT 值会发生变化？

问题描述

SQLite 数据库在这里

我的问题是，每个员工支持了多少客户，每次销售的平均收入是多少，他们的总销售额是多少？

为了让每个员工支持多少客户，我的代码是这样的，

SELECT employees.EmployeeId, customers.SupportRepId, COUNT(customers.CustomerId) "Customers supported"
FROM customers
JOIN employees ON employees.EmployeeId = customers.SupportRepId
GROUP BY customers.SupportRepId;

我得到的结果是这样的：

+------------+--------------+----------------------+
| EmployeeID | SupportRepID | Customers Supported  |
+------------+--------------+----------------------+
|          3 |            3 |                   21 |
|          4 |            4 |                   20 |
|          5 |            5 |                   18 |
+------------+--------------+----------------------+

为了获得总收入和平均收入，我更新了我的代码，如下所示：

SELECT employees.EmployeeId, customers.SupportRepId, COUNT(customers.CustomerId) 'Customers Supported', SUM(invoices.Total) 'Total Revenue', SUM(invoices.Total)/COUNT(customers.CustomerId) 'Average Revenue'
FROM customers
JOIN employees ON employees.EmployeeId = customers.SupportRepId
JOIN invoices ON invoices.CustomerId = customers.CustomerId
GROUP BY customers.SupportRepId;

我得到的结果是这样的：

+------------+--------------+---------------------+------------------+------------------+
| EmployeeID | SupportRepID | Customers Supported |  Total Revenue   | Average Revenue  |
+------------+--------------+---------------------+------------------+------------------+
|          3 |            3 |                 146 | 833.040000000001 | 5.70575342465754 |
|          4 |            4 |                 140 | 775.400000000001 | 5.53857142857144 |
|          5 |            5 |                 126 | 720.160000000001 | 5.71555555555556 |
+------------+--------------+---------------------+------------------+------------------+

更新我的代码后，支持的客户列发生了变化，我相信以前的数据是正确的。因此，我的平均收入与新值不正确。为什么客户支持的数据会发生变化？我怎样才能解决这个问题？

标签： sqldatabasesqlite

解决方案

我DISTINCT在 COUNT 函数中添加了一个函数，它起作用了。

SELECT employees.EmployeeId, customers.SupportRepId, COUNT(DISTINCT customers.CustomerId) 'Customers Supported', SUM(invoices.Total) 'Total Revenue', SUM(invoices.Total)/COUNT(DISTINCT customers.CustomerId) 'Average Revenue'
FROM customers
JOIN employees ON employees.EmployeeId = customers.SupportRepId
JOIN invoices ON invoices.CustomerId = customers.CustomerId
GROUP BY customers.SupportRepId;

如果没有 DISTINCT 函数，它会计算重复的客户 ID。

推荐阅读