请求 SQL 帮助：SELECT 表 A 与表 B 连接（最大或空）

Question

在表 A（别名 ai）中，我有一个项目列表。在表 B（别名 aib）中，我有表 A 中所有商品的价格历史记录，但不是所有商品，对于这些商品，我需要 SELECT NULL。我需要从表 A 中选择商品，它是表 B 中的最高价格或 NULL。

这是我所拥有的，但它不太有效，如果表 B 具有广泛的价格历史记录，我会得到重复的记录。

这是不完全有效的查询：

SELECT *

FROM ItemGroup aig 

INNER JOIN Item ai ON ai.groupID = aig.ID 

LEFT JOIN Item_Prices aib ON ai.ID = aib.ItemID 

WHERE aig.ID = @groupID AND 
(aib.max_price = (SELECT MAX(price) AS highprice FROM Item_Prices 
  WHERE ItemID = ai.ID) 
  OR aib.price IS NULL) 

ORDER BY end_date

欢迎所有建议。

尊敬的

雷

Answer 1

@squillman 几乎已经成功了。优化：

SELECT GroupID      = aig.groupID     ,
       ItemID       = ai.ID as ItemID ,
       HighestPrice = aib.HighestPrice
FROM      Item        ai
left join ( select ItemID ,
                   HighestPrice = max(price)
            from Item_Prices
            group by ItemID
          ) aib on aib.ItemID = ai.ID
WHERE ai.groupID = @groupID
ORDER BY end_date

如果您从表 ItemGroup 中看到的只是组 id...您不需要该表：您已经在 Item 表中获得了 groupID，因此 ItemGroup 是不必要的，尽管您需要如果您需要 ItemGroup 中的其他属性，请保留它。保留 ItemGroup 不会对查询计划产生太大影响：您最多需要一个组，并且大概 ItemGroup 在 groupID 上有一个覆盖索引。

子查询不需要 distinct：group by ItemID 使子查询结果集通过 ItemID 唯一。这对性能有影响：distinct 意味着引擎必须确保结果集是唯一的。希望优化器能够意识到group by使集合唯一并丢弃distinct，但我不会指望它。

Answer 2

Ray，您只是使用项目表将两个表连接在一起吗？如果您不需要项目表中的任何其他内容来完成查询的其余部分，那么它并不是真正必要的。

如果您真正想要做的全部是“从表 A 中选择商品，它是表 B 中的最高价格或 NULL”。如果 Item_Prices.price 允许 null 的话，它可能会像这样简单。

SELECT a.ItemName, MAX(b.price) AS 'MaxPrice' 
FROM Item AS a INNER JOIN Item_Prices AS b
ON a.groupID = b.ItemID
GROUP BY a.ItemName

这是我的 Item_Prices 表中的内容

这是我的 Item 表中的内容

查询结果：

这是我的 Item 表的设计视图，groupID 是主键。

这是我的 Item_Prices 表的设计视图，您可以将 ItemID 设置为外键

如果您想从单独的表中选择数据，则不需要它第三张桌子为您或其他任何事物将它们链接起来。只要它们在表 A（主键）中具有某种类型的唯一标识符，并且在表 B 中具有匹配的键（通常是外键）链接起来，那么您就是黄金！

如果您确实打算将第三个表放在那里用于另一个领域，那么请忽略！我只是想举一个简单的例子来向您展示这一点，因为当我在工作/学校时，我看到了很多大多数时候，人们使用第三个表将表链接在一起，这是没有意义的！

Answer 3

SELECT *
FROM ItemGroup aig
INNER JOIN Item ai ON ai.groupID = aig.ID
LEFT OUTER JOIN (
    SELECT 
        ItemID,MAX(max_price)
    FROM Item_Prices
    GROUP BY ItemID
) aib ON ai.ID=aib.ItemID
WHERE aig.ID = @groupID
ORDER BY end_date

子查询通过 ItemID 获取最高价格，因此应该过滤掉重复项。

Answer 4

Select ...
From ItemGroup As AIG
    Join Item As AI
        On AI.ID = AIG.ID
Where AIG.Id = @groupId
    And Exists  (
                Select 1
                From Item_Prices As IP1
                    Join    (
                            Select IP2.ItemID, Max( IP2.Price ) As Price
                            From Item_Prices As IP2
                            Group By IP2.ItemId
                            ) As MaxPrices
                        On MaxPrices.ItemID = IP1.ItemID
                            And MaxPrices.Price = IP2.Price
                Where IP1.ItemID = AI.ID
                Union All
                Select 1
                From Item_Prices As IP1
                Where IP1.ItemID = AI.ID
                    And IP1.Price Is Null
                )
Order By End_Date