仅使用 t-sql 加入“最新”记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4462821/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join to only the "latest" record with t-sql
提问by Joel Martinez
I've got two tables. Table "B" has a one to many relationship with Table "A", which means that there will be many records in table "B" for one record in table "A".
我有两张桌子。表“B”与表“A”是一对多的关系，这意味着表“A”中的一条记录在“B”表中会有很多记录。
The records in table "B" are mainly differentiated by a date, I need to produce a resultset that includes the record in table "A" joined with only the latestrecord in table "B". For illustration purpose, here's a sample schema:
表“B”中的记录主要由日期区分，我需要生成一个结果集，其中包含表“A”中的记录，仅与表“B”中的最新记录相连。出于说明目的，这里有一个示例架构：
Table A
-------
ID
Table B
-------
ID
TableAID
RowDate
I'm having trouble formulating the query to give me the resultset I'm looking for any help would be greatly appreciated.
我在制定查询以提供我正在寻找的任何帮助的结果集时遇到问题将不胜感激。
回答by Philip Fourie
SELECT *
FROM tableA A
OUTER APPLY (SELECT TOP 1 *
FROM tableB B
WHERE A.ID = B.TableAID
ORDER BY B.RowDate DESC) as B
回答by RedFilter
select a.*, bm.MaxRowDate
from (
select TableAID, max(RowDate) as MaxRowDate
from TableB
group by TableAID
) bm
inner join TableA a on bm.TableAID = a.ID
If you need more columns from TableB, do this:
如果您需要来自 TableB 的更多列，请执行以下操作：
select a.*, b.* --use explicit columns rather than * here
from (
select TableAID, max(RowDate) as MaxRowDate
from TableB
group by TableAID
) bm
inner join TableB b on bm.TableAID = b.TableAID
and bm.MaxRowDate = b.RowDate
inner join TableA a on bm.TableAID = a.ID
回答by Joel Coehoorn
With ABDateMap AS (
SELECT Max(RowDate) AS LastDate, TableAID FROM TableB GROUP BY TableAID
),
LatestBRow As (
SELECT MAX(ID) AS ID, TableAID FROM ABDateMap INNER JOIN TableB ON b.TableAID=a.ID AND b.RowDate = LastDate GROUP BY TableAID
)
SELECT columns
FROM TableA a
INNER JOIN LatestBRow m ON m.TableAID=a.ID
INNER JOIN TableB b on b.ID = m.ID
回答by gbn
table B join is optional: it depends if there are other columns you want
表 B 连接是可选的：这取决于您是否需要其他列
SELECT
*
FROM
tableA A
JOIN
tableB B ON A.ID = B.TableAID
JOIN
(
SELECT Max(RowDate) AS MaxRowDate, TableAID
FROM tableB
GROUP BY TableAID
) foo ON B.TableAID = foo.TableAID AND B.RowDate= foo.MaxRowDate
回答by Y.B.
Just for the clarity's sake and to benefit those who will stumble upon this ancient question. The accepted answer would return duplicate rows if there are duplicate RowDatein Table B. A safer and more efficient way would be to utilize ROW_NUMBER():
只是为了清楚起见，并使那些偶然发现这个古老问题的人受益。如果 中存在重复RowDate，则接受的答案将返回重复的行Table B。一种更安全、更有效的方法是利用ROW_NUMBER()：
Select a.*, b.* -- Use explicit column list rather than * here
From [Table A] a
Inner Join ( -- Use Left Join if the records missing from Table B are still required
Select *,
ROW_NUMBER() OVER (PARTITION BY TableAID ORDER BY RowDate DESC) As _RowNum
From [Table B]
) b
On b.TableAID = a.ID
Where b._RowNum = 1
回答by Paulo Marques
Try using this:
尝试使用这个：
BEGIN
DECLARE @TB1 AS TABLE (ID INT, NAME VARCHAR(30) )
DECLARE @TB2 AS TABLE (ID INT, ID_TB1 INT, PRICE DECIMAL(18,2))
INSERT INTO @TB1 (ID, NAME) VALUES (1, 'PRODUCT X')
INSERT INTO @TB1 (ID, NAME) VALUES (2, 'PRODUCT Y')
INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (1, 1, 3.99)
INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (2, 1, 4.99)
INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (3, 1, 5.99)
INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (1, 2, 0.99)
INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (2, 2, 1.99)
INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (3, 2, 2.99)
SELECT A.ID, A.NAME, B.PRICE
FROM @TB1 A
INNER JOIN @TB2 B ON A.ID = B.ID_TB1 AND B.ID = (SELECT MAX(ID) FROM @TB2 WHERE ID_TB1 = A.ID)
END
回答by Bhaskararao Gummidi
This will fetch the latest record with JOIN. I think this will help someone
这将使用 JOIN 获取最新记录。我认为这会帮助某人
SELECT cmp.*, lr_entry.lr_no FROM
(SELECT * FROM lr_entry ORDER BY id DESC LIMIT 1)
lr_entry JOIN companies as cmp ON cmp.id = lr_entry.company_id
