有限的 T-SQL 连接

Question

这应该很简单，但是我的大脑不知怎么回事停止了工作。

我有两个相关的表：

表1：

ID (PK), Value1

表格2：

BatchID, Table1ID (FK to Table 1 ID), Value2

示例数据：

表格1：

ID  Value1
1   A
2   B

表格2：

BatchID  Table1ID  Value2
1        1         100
2        1         101
3        1         102
1        2         200
2        2         201

现在，对于表1中的每条记录，我想在表2中进行匹配，但只匹配最近的一条记录（批次ID是连续的）。上述示例的结果如下：

Table1.ID  Table1.Value1  Table2.Value2
1          A              102
2          B              201

问题很简单，如何限制与Table2的连接结果。在SO上有类似的问题，但找不到像我的这样。这是一个MySQL上的例子： LIMITing an SQL JOIN 我接受任何方法，尽管速度仍然是主要优先级，因为它将是一个大型数据集。

- Adrian Godong

Answer 1

WITH Latest AS (
    SELECT Table1ID
        ,MAX(BatchID) AS BatchID
    FROM Table2
    GROUP BY Table1ID
)
SELECT *
FROM Table1
INNER JOIN Latest
    ON Latest.Table1ID = Table1.ID
INNER JOIN Table2
    ON Table2.BatchID = Latest.BatchID

- Cade Roux

---

“SQL-Server”这个标签不是表示使用微软的SQL Server吗？那么使用Oracle的“With”子句不就无效了吗？ - madcolor

公共表达式（CTE）是在SQL Server 2005中引入的，这个答案是正确的。 - TheTXI

Answer 2

SELECT  id, value1, value2
FROM    (
        SELECT  t1.id, t2.value1, t2.value2, ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.BatchID DESC) AS rn
        FROM    table1 t1
        JOIN    table2 t2
        ON      t2.table1id = t1.id
        ) q
WHERE   rn = 1

- Quassnoi

---

你只需在“包含执行计划”的同一窗口中运行这两个语句，然后你就会得到每个语句的百分比代价。 - Joel Mansford

太棒了！结果完全一致。 - Adrian Godong

如果不使用CTE，而必须重复相同的子查询，则查询计划仍将与CTE一样好。 CTE的主要优点是用于更复杂的堆叠、DRY和维护。 - Cade Roux

顺便说一句，@Adrian，这是一个派生表而不是子查询。 - HLGEM

Answer 3

尝试

select t1.*,t2.Value2
from(
select Table1ID,max(Value2) as Value2
from [Table 2]
group by Table1ID) t2
join [Table 1] t1 on t2.Table1ID = t1.id

- SQLMenace

Answer 4

按最近时间分组或筛选WHERE子句：

SELECT * FROM Table1 a
INNER JOIN Table2 b ON (a.id = b.Table1ID)
WHERE NOT EXISTS(
      SELECT 1 FROM Table2 c WHERE c.Table1ID = a.id AND c.BatchID > b. BatchID
)

- instanceof me

---

问题在于，“最近的”对于每个记录可能是不同的，因此您不能为整个表格采用批发数字。 - Adrian Godong

-1 是因为它只是一个通用的回答尝试。此外，您可以看到纯 GROUP BY/WHERE 在这里行不通。您需要一个 CTE，就像 Cade 所做的那样，或者一个子查询。 - Eric

我正在添加一个例子。同意我应该更清楚地说明子查询是“筛选最近的WHERE子句”。 - instanceof me