时间戳

首页 > 解决方案 > 由于某些原因，聚合函数在连接中无法正常工作

sql - 由于某些原因，聚合函数在连接中无法正常工作

问题描述

我遇到了当前连接的问题。由于某些原因，聚合函数在其中返回不正确的数量。当我运行以下查询时，结果中没有任何行： 

 SELECT 
   CONCAT( pp.FirstName, ' ', pp.LastName) AS 'CustomerName', 
   ssoh.Status AS 'OrderStatus',
   FORMAT (ssoh.OrderDate, 'yyyy-dd-MM') AS OrderDate, ItemCount, AvgQty
 FROM Sales.SalesOrderHeader AS ssoh
 JOIN Person.Person AS pp ON pp.BusinessEntityID = ssoh.CustomerID
 JOIN ( 
   SELECT SalesOrderID, SUM(ssod.OrderQty) AS ItemCount, AVG(ssod.OrderQty) AS AvgQty
   FROM Sales.SalesOrderDetail AS ssod
   GROUP BY ssod.SalesOrderID
   HAVING SUM(ssod.OrderQty) > 300 
 ) AS ssod ON ssoh.SalesOrderID = ssod.SalesOrderID
 ORDER BY ssod.ItemCount DESC

当我单独运行此查询时：

Select SUM(OrderQty), SalesOrderID
From Sales.SalesOrderDetail
Where SUM(OrderQty) > 300
Group BY SalesOrderID
Order by desc
go

我得到了一些行，这意味着我的思维方式没有错，我在脚本中犯了错误。请帮帮我。

标签： sqltsqlsumaverageaggregate-functions

解决方案

你需要在这里使用没有：

Select SUM(OrderQty) as s, SalesOrderID
From Sales.SalesOrderDetail
Group BY SalesOrderID
HAVING SUM(OrderQty) > 300
Order by s desc

编辑以获得在 SQL Server 上工作的顺序

SELECT x.s, x.salesorderid
FROM (
  Select SUM(OrderQty) as s, SalesOrderID
  From Sales.SalesOrderDetail
  Group BY SalesOrderID
  HAVING SUM(OrderQty) > 300
) x
Order by x.s desc

推荐阅读