代码之家 › 专栏 › 技术社区 › nam

使用子查询或INNER JOIN

t-sql sql-server

nam · 技术社区 · 5 月前

第2项挑战 this Lab 来自Microsoft SQL Server团队的信息如下:

挑战1 :检索产品价格信息

AdventureWorks产品每个都有一个标准成本价,表示产品的制造成本,以及 list price 这表示产品的推荐售价。这些数据存储在 SalesLT.Product 桌子。每当订购产品时 actual unit price 出售时间也记录在 SalesLT.SalesOrderDetail 桌子。您必须使用子查询将每个产品的成本和标价与每次销售中收取的单价进行比较。

检索标价高于平均单价的产品。

检索每个产品的产品ID、名称和标价,其中标价高于所有已售出产品的平均单价。
检索标价为100或以上但售价低于100的产品。

检索每个产品的产品ID、名称和标价,其中标价为100或更高,且产品售价低于100。

针对上述挑战的解决方案(在上面的同一链接上提供),他们提出了以下查询,返回7条记录:

 SELECT ProductID, Name, ListPrice
 FROM SalesLT.Product
 WHERE ProductID IN (SELECT ProductID
                     FROM SalesLT.SalesOrderDetail
                     WHERE UnitPrice < 100.00)
   AND ListPrice >= 100.00
 ORDER BY ProductID;

产品ID	姓名	列表价格
810	HL山把手	120.27
813	HL道路把手	120.27
876	挂接架-4辆自行车	120
894	后拨链器	121.46
907	后刹车	106.5
948	前刹车	106.5
996	HL底部支架	121.49

但是,使用相同的搜索条件,我自己的以下查询返回了27条记录(如下所示),其中 list prices >=100 和 UnitPrice < 100 :

问:为什么微软实验室建议的解决方案是正确的,而我所做的是不正确的:

SELECT p.ProductID, p.Name, p.ListPrice, s.UnitPrice
FROM SalesLT.Product p
JOIN SalesLT.SalesOrderDetail s ON p.ProductID = s.ProductID  
WHERE p.ListPrice >= 100.00
  AND s.UnitPrice < 100.00
ORDER BY ProductID;

产品ID	姓名	列表价格	單價
810	HL山把手	120.27	72.162
810	HL山把手	120.27	72.162
810	HL山把手	120.27	72.162
813	HL道路把手	120.27	72.162
813	HL道路把手	120.27	72.162
813	HL道路把手	120.27	72.162
876	挂接架-4辆自行车	120	72
876	挂接架-4辆自行车	120	72
876	挂接架-4辆自行车	120	72
876	挂接架-4辆自行车	120	72
876	挂接架-4辆自行车	120	72
876	挂接架-4辆自行车	120	72
876	挂接架-4辆自行车	120	72
876	挂接架-4辆自行车	120	72
894	后拨链器	121.46	72.876
894	后拨链器	121.46	72.876
907	后刹车	106.50	63.90
948	前刹车	106.50	63.90
948	前刹车	106.50	63.90
948	前刹车	106.50	63.90
948	前刹车	106.50	63.90
948	前刹车	106.50	63.90
948	前刹车	106.50	63.90
948	前刹车	106.50	63.90
996	HL底部支架	121.49	72.894
996	HL底部支架	121.49	72.894
996	HL底部支架	121.49	72.894
996	HL底部支架	121.49	72.894

1 回复 | 直到 5 月前

Lajos Arpad 5 月前

首先,让我们有点迂腐,考虑一下任务要求你做什么和你做了什么之间的区别。该任务要求您使用subselect,并使用main select查询 SalesLT.Product 以及子选择查询 SalesLT.SalesOrderDetail 。你没有按照任务指示去做,而是做了 join 。从技术上讲,你可以通过以下方式实现任务的目标 参加 同样,但您需要按照说明进行操作,如果要求您使用子查询,请这样做。

其次,你犯的逻辑错误是,当你加入时 SalesLt.Product 具有 销售LT。销售订单详细信息 ,然后您将加入所有匹配,因此,如果您有一个产品售出了8次,那么您将获得8对,因此您将有8条与该产品对应的记录,而不是1条(请参阅 ProductID 例如876),如果你遵循指令并使用子查询,这将是结果。但是,为了实现同样的目标 参加 ,你可以做 group by 也就是说,您将多个产品销售元组合并为单个记录,每个产品只有一个匹配项:

SELECT p.ProductID, p.Name, p.ListPrice
FROM SalesLT.Product p
JOIN SalesLT.SalesOrderDetail s ON p.ProductID = s.ProductID  
WHERE p.ListPrice >= 100.00
  AND s.UnitPrice < 100.00
GROUP BY p.ProductID, p.Name, p.ListPrice
ORDER BY ProductID;

第三,你的 参加 并且它们的子选择不是最优的,因为它们将搜索所有对而不是单个示例。 EXISTS 在子选择中搜索单个匹配项,如果找到匹配项,则停止搜索新的匹配项,而 IN 找到所有匹配项,因此需要更多时间,在我们的情况下,我们只对 存在 ,所以:

 SELECT ProductID, Name, ListPrice
 FROM SalesLT.Product
 WHERE EXISTS (SELECT SalesLT.SalesOrderDetail.ProductID
               FROM SalesLT.SalesOrderDetail
               WHERE UnitPrice < 100.00 AND SalesLT.SalesOrderDetail.ProductID = SalesLT.Product.ProductID)
   AND ListPrice >= 100.00
 ORDER BY ProductID;

第四,第一项任务尚未讨论。它看起来与他们的子选区有些相似:

 SELECT ProductID, Name, ListPrice
 FROM SalesLT.Product
 WHERE ListPrice > (SELECT AVG(UnitPrice)
                    FROM SalesLT.SalesOrderDetail
                    WHERE SalesLT.Product.ProductID = SalesLT.SalesOrderDetail.ProductID)
ORDER BY ProductID;