时间戳

首页 > 解决方案 > SQL Server - SELECT 语句不返回结果

sql - SQL Server - SELECT 语句不返回结果

问题描述

使用 AdventureWorks 数据库，我收到了一个问题：“列出订单客户名称、订单状态、订购日期、订单商品数量以及订单商品数量大于 300 的平均订购数量”。但是，我下面的 SELECT 语句没有返回任何结果......我做错了什么？

SELECT scpii.LastName + ', ' + scpii.FirstName AS 'Customer Name', ssoh.Status AS 'Order Status', ssoh.OrderDate AS 'Date Ordered', SUM (ssod.OrderQty) AS 'Count of Items', AVG (ssod.OrderQty) AS 'Average Quantity'
FROM Sales.CustomerPII scpii
INNER JOIN Sales.SalesOrderHeader ssoh
ON ssoh.CustomerID = scpii.CustomerID
INNER JOIN Sales.SalesOrderDetail ssod
ON ssod.SalesOrderID = ssoh.SalesOrderID
GROUP BY scpii.LastName, scpii.FirstName, ssoh.Status, ssoh.OrderDate, ssod.OrderQty
HAVING SUM(ssod.OrderQty) > 300;

标签： sqlsql-serveradventureworks

解决方案

  • 您不需要在ProductID和上进行分组Orderqty。如果您对它们进行分组，您会将其分组到单个订单项目级别。所以你永远数不过300
  • 订单上的商品数量实际上应该是Orderqty.

尝试：

SELECT scpii.LastName + ', ' + scpii.FirstName AS 'Customer Name', 
       ssoh.Status AS 'Order Status', 
       ssoh.OrderDate AS 'Date Ordered', 
       SUM (ssod.OrderQty) AS 'Count of Items', 
       AVG (ssod.OrderQty) AS 'Average Quantity'
FROM Sales.CustomerPII scpii
INNER JOIN Sales.SalesOrderHeader ssoh
  ON ssoh.CustomerID = scpii.CustomerID
INNER JOIN Sales.SalesOrderDetail ssod
  ON ssod.SalesOrderID = ssoh.SalesOrderID
GROUP BY scpii.LastName, 
         scpii.FirstName, 
         ssoh.Status, 
         ssoh.OrderDate 
HAVING SUM (ssod.OrderQty) > 300;

推荐阅读