请求的SQL帮助:选择表A与表B联接(Max或Null)

Question

在表A(别名ai)中，我有一个项目列表。在表B(别名aib)中，我有表A中所有项目的价格历史，但不是所有项目的价格历史，对于这些项目，我需要选择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...you不需要该表:您已经在Item表中获得了groupID，所以ItemGroup是不必要的，不过如果您需要来自ItemGroup的其他属性，则需要保留它。保留ItemGroup不会对查询计划产生太大影响:您最多需要一个组，并且假设ItemGroup在groupID上有一个覆盖索引。

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

Answer 2

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 3

Ray，你只是在使用item表将两个表连接在一起吗？如果查询的其余部分不需要item表中的任何其他内容，那么就没有必要这样做。

如果你真正想做的就是“ the item from table A，it's maximum price from table B or ”。如果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表的设计视图，groupID是主键。

​

​

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

​

​

如果您想从不同的表中选择数据，则不需要第三个表为您将它们链接起来。只要它们在表A中有某种类型的唯一标识符(主键)，并且在表B中有一个匹配的键(通常是外键)，那么您就是黄金！

如果你真的打算把第三个表用在另一个字段中，那就忽略它吧！我只是想举一个简单的例子来告诉你，因为当我在工作/学校的时候，我看到很多人使用第三个表链接表，这在大多数情况下是没有意义的！