sql - SQL查询-一列必须是不同的，受限列必须是最新的

Question

我有一个CATEGORIES表，该表链接到ITEMS表（一对多）。每个项目表在BARCODES表中可以有多个条形码（一对多）。

这样的想法是，可以在某个时候在商品上添加新条形码，但是旧数据存储在BARCODES表中，因此，如果使用去年数据的订单进行搜索，则旧条形码的商品和类别仍然可以很快找到。

希望这是有道理的-这有点过分简化了。

我的表格示例：

CATEGORIES TABLE
ID   NAME        CODE    ACCOUNTING_REFERENCE
1    Beverages   BEV     Stock_Beverages
2    Pies        PIE     Stock_Pies
3    Chips       CHP     Stock_Chips

ITEMS TABLE
ID   CATEGORY_ID  DESCRIPTION                 BASE_COST
1    1            Red Bull (single)           $4.50
2    2            Ponsonby Pie - Mince Cheese $2.99
3    1            Coke Can (single)           $3.50
4    2            Big Ben - Steak Pepper      $1.99

BARCODES TABLE
ID  ITEM_ID  BARCODE   ACTIVE_FROM
1   1        XSD123    2009/10/11
2   2        AXF123    2009/10/12
3   3        XYZ234    2009/10/11
4   1        NEW001    2010/01/05
5   1        NEW002    2010/01/05*

我知道在这种情况下在同一天输入两个条形码是没有意义的。忽略这看起来像不良数据。这个例子只是我在做的一个比喻-在反映此结构的现实世界应用程序中，在同一天输入两个“条形码”是有意义的。我想公开讨论真实数据，但我不能随意这样做-如果感到困惑，我们深表歉意。


我正在尝试向用户显示项目信息。我想从SQL返回每一项针对所有项目的一条记录。返回的信息应包括行的类别名称，代码和会计参考-这些列的重复项可以。查询需要返回物料描述和基本成本。

我能做的很多。

我还希望查询返回该项目的最新条形码。在同一天为一个商品输入两个条形码的情况下，显示哪个条形码并不重要-尽管显示两个（或多个）BARCODE.ID字段中的较高者会很不错。

另外，可能存在的项目可能没有针对它们的任何条形码，在这种情况下，我想返回一个空条目。

我想要的结果集如下所示：

ITEM_ID   DESCRIPTION                  BASE_COST  CATEGORY_NAME CATEGORY_CODE  ACCOUNTING_REFERENCE  BARCODE
1         Red Bull (single)            $4.50      Beverages     BEV            Stock_Beverages       NEW002
2         Ponsonby Pie - Mince Cheese  $2.99      Pies          PIE            Stock_Pies            AXF123
3         Coke Can (single)            $3.50      Beverages     BEV            Stock_Beverages       XYZ234
4         Big Ben - Steak Pepper       $1.99      Pies          PIE            Stock_Pies            <null>

给定上面的表格，我不知道如何将BARCODE列添加到此结果集中。

万一我不清楚：我需要知道如何构造一个SQL查询，该查询将根据给定的数据准确地给出上面的结果。

我的结果集中的ITEM_ID列必须是不同的。我不仅可以限制内存中的陈旧条目，而且如果我冒泡多个ITEM_ID，它会破坏我正在修改的应用程序中其他地方使用的PK / FK关系。

作为记录，我在网站上浏览了与不同的sql列相关的所有内容，以回答此问题。我找到了很多条目，但似乎找不到任何建议的解决方案对我有用。也许我只是很稠密-天色已经晚了，我可能并没有想清楚。抱歉，如果我错过了明显的事情。

编辑

Gabe在下面给出了一个很好的答案，我意识到我应该更加清楚。

Gabe的答案对我不起作用，因为偶尔在我的数据中，我有两个条形码针对相同项目的相同时间戳。当我尝试使用他的代码时，当一件物品在同一天被贴上两个条形码时，我最终会退还多件物品。

这确实是违反直觉的，所以这是我无法正确沟通的错。本质上，上述场景实际上并不是我正在使用的数据。我不能随意讨论我正在公开使用的数据库，因此我必须重命名所使用示例中的所有内容。

我已经调整了上面的问题。我知道用相同的时间戳输入两个条形码似乎很可笑-请放心，这有可能在实际数据库中发生。

再次编辑

Gabe和simon的答案都是正确的。我之所以选择盖比（Gabe）的答案，仅仅是因为我发现他的陈述最清晰。

就是说，我也喜欢simon，因为它显示了我不熟悉的使用SQL的语法-很高兴看到实际中不熟悉的语法。

在某些时候，我需要对这两种方法进行基准测试，以查看哪种方法更快。就我的样本数据而言，它们目前是相等的-尽管我希望我可以通过一些工作来改变这一点，以在数据库中填充数千条记录并修改索引。

谢谢您的帮助。

Answer 1

听起来您想要关联的子查询。大致来说：

 select *
from ITEMS
join BARCODES as B on ITEMS.ID = B.ITEM_ID
join CATEGORIES on CATEGORIES.ID = ITEMS.CATEGORY_ID
where B.ACTIVE_FROM =
    (select max(ACTIVE_FROM)
     from BARCODES as B2
     where B2.ITEM_ID = B.ITEM_ID)

要处理给定项目没有条形码的情况，您需要外部联接，而在有许多条形码的情况下，只需要提供一个条形码，则需要一个附加子查询。在ANSI SQL中，可能看起来像这样：

 select *
from ITEMS
LEFT OUTER join BARCODES as B on ITEMS.ID = B.ITEM_ID
join CATEGORIES on CATEGORIES.ID = ITEMS.CATEGORY_ID
where B.ACTIVE_FROM =
    (select max(ACTIVE_FROM)
     from BARCODES as B2
     where B2.ITEM_ID = B.ITEM_ID)
    AND B.ID =
    (SELECT MAX(ID)
     FROM BARCODES AS B3
     WHERE B3.ITEM_ID = B.ITEM_ID)
    OR ACTIVE_FROM IS NULL