SQL查询。一个不同寻常的连接。用sqlite-3实现的数据库

Question

这本质上是一个关于构造SQL查询的问题。db是用sqlite3实现的。我是一个比较新的SQL用户。

我有两张表，想以一种不同寻常的方式加入其中。下面是一个例子来解释这个问题。

表1 (t1)：

id  year    name
-------------------------
297 2010    Charles
298 2011    David
300 2010    Peter
301 2011    Richard

表2 (t2)

id  year    food
---------------------------
296 2009    Bananas
296 2011    Bananas
297 2009    Melon
297 2010    Coffee
297 2012    Cheese
298 2007    Sugar
298 2008    Cereal
298 2012    Chocolate
299 2000    Peas
300 2007    Barley
300 2011    Beans
300 2012    Chickpeas
301 2010    Watermelon

我想加入id和年份的表。问题是( 1 ) id必须完全匹配，但是如果表2中没有与表1中的年份完全匹配的年份，那么我希望选择下一个(更低的)可用年份。我想要生成的类型的选择将产生以下结果

id  year    matchyr name    food
-------------------------------------------------
297 2010    2010    Charles Coffee
298 2011    2008    David   Cereal
300 2010    2007    Peter   Barley
301 2011    2010    Richard Watermelon

总而言之，id=297与表1中给出的year=2010完全匹配，因此从表2中选择id=297的对应行year=2010。id=298，year=2011在表2中没有匹配的年份，因此选择下一个可用年份(少于2011年)。正如您所看到的，我还想知道匹配的年份(无论是确切的还是不准确的)实际上是什么。

我非常感谢(1)一个指示(是/否答案)，说明这是否可以仅在SQL中完成，或者我是否需要查看SQL之外的东西，以及(2)一个解决方案，如果这不是太麻烦的话。

Answer 1

当然，您可以在SQL中做到这一点。第一步需要的是一个查询，它连接id上的t1和t2，并给出各种可能的年份(EDIT:修正了这句话，原来的一句话也包含了下一步)。您可以创建一个名为t3的视图，该视图如下所示：

CREATE VIEW [t3] AS SELECT t1.id as id, t2.year as year FROM t1 INNER JOIN t2 ON (t1.id = t2.id AND T2.year <= t1.year)

在这里，您需要从每个id的t3中获取最大年份：

CREATE VIEW [t4] AS SELECT id, MAX(year) FROM t3 GROUP BY id

您应该能够将t4重新加入到t1和t2上，以获得您想要的东西。当然，有一些方法可以缩短这段时间，但这应该会让你沿着正确的思路思考。另外，我猜这只是一个例子，但请不要将您的表称为t1、t2等。