sql-server - 如何选择列和聚合函数？

Question

假设我有一张公司表:

Company
coID | coName | coCSR

coCSR字段是与帐户处理程序表相关的数字 ID:

AccountHandler
ahID | ahFirstName | ahLastName

我还有一张订单表:

Order
orID | orCompanyID | orDate | orValue

现在我需要生成的输出结构如下:

Company | Account handler | No. of orders | Total of orders

这是我尝试过的查询，它会产生错误:

SELECT coID, coName, ahFirstName+' '+ahLastName AS CSRName, COUNT(orID) AS numOrders, SUM(orValue) AS totalRevenue
FROM Company
LEFT JOIN AccountHandler ON coCSR = ahID
LEFT JOIN Order ON coID = orCompanyID
WHERE coCSR = 8
AND orDate > getdate() - 365
ORDER BY coName ASC

错误是: 列名“AccountHandler.ahLastName”在 ORDER BY 子句中无效，因为它不包含在聚合函数中并且没有 GROUP BY 子句。

如果我使用 GROUP BY coID , 我得到 关键字“WHERE”附近的语法不正确。 如果我更改 WHERE至 HAVING由于聚合函数的原因，我收到错误消息，告诉我删除未包含在聚合函数或 GROUP BY 子句中的每个其他列名。

我不得不承认，除了最基本的 SQL 命令之外，我还不了解任何东西的逻辑和语法，我只是试图应用我以前见过的东西，但它不起作用。请帮我解决这个问题。更好的是，你能帮我理解为什么它现在不起作用吗？ :)

Answer 1

一方面，您的查询可能缺少 FROM Company ，但是在您撰写帖子时可能会以某种方式丢失。

您似乎正在按公司汇总数据。因此，您需要按公司分组。您尝试分组失败的最可能原因可能是因为您放置了 GROUP BY在错误的地方。我想你把它放在WHERE之前，但实际上它应该在它之后(和之前 ORDER BY ):

SELECT
  c.coID,
  c.coName,
  a.ahFirstName + ' ' + a.ahLastName AS CSRName,
  COUNT(o.orID) AS numOrders,
  SUM(o.orValue) AS totalRevenue
FROM Company c
LEFT JOIN AccountHandler a ON c.coCSR = a.ahID
LEFT JOIN [Order] o ON c.coID = o.orCompanyID
WHERE c.coCSR = 8
AND o.orDate > getdate() - 365
GROUP BY ...
ORDER BY c.coName ASC

另一个问题是，按什么分组。 SQL Server 要求在 GROUP BY 中指定所有非聚合列.因此您的 GROUP BY子句应如下所示:

GROUP BY
  c.coID,
  c.coName,
  a.ahFirstName,
  a.ahLastName

请注意，您不能通过在 SELECT 中分配给它们的别名来引用列。子句(例如 CSRName )。但是你可以使用 ahFirstName+' '+ahLastName表达式而不是相应的列，在这种特殊情况下不会有任何区别。

如果您需要向此查询添加更多非聚合列，则必须将它们都添加到 SELECT并到 GROUP BY .在某些时候，这可能会变得有点乏味。我建议您尝试以下方法:

SELECT
  c.coID,
  c.coName,
  a.ahFirstName + ' ' + a.ahLastName AS CSRName,
  ISNULL(o.numOrders,    0) AS numOrders,
  ISNULL(o.totalRevenue, 0) AS totalRevenue
FROM Company c
LEFT JOIN AccountHandler a ON c.coCSR = a.ahID
LEFT JOIN (
  SELECT
    orCompanyID,
    COUNT(orID)  AS numOrders,
    SUM(orValue) AS totalRevenue
  FROM [Order]
  GROUP BY
    orCompanyID
  WHERE orDate > getdate() - 365
) o ON c.coID = o.orCompanyID
WHERE c.coCSR = 8
ORDER BY c.coName ASC

也就是说，聚合是在 Order 上完成的。仅表。然后将聚合行集连接到其他表。您现在可以从 Company 向输出中提取更多属性。或 AccountHandler无需担心将它们添加到 GROUP BY因为在那个级别不再需要分组。