连接到只有“最新”记录的t-sql。

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 latest record in table "B". For illustration purpose, here's a sample schema:

表“B”中的记录主要是由日期区分的，我需要生成一个resultset，其中包括表“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.

我在制定查询时遇到了困难，无法给出我需要的结果集。

6 个解决方案

#1

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:

如果您需要表b中的更多列，请执行以下操作:

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.TableA = b.TableA
    and bm.MaxRowDate = b.MaxRowDate
inner join TableA a on bm.TableAID = a.ID

#2

SELECT *
FROM tableA A
OUTER APPLY (SELECT TOP 1 * 
             FROM tableB B
             WHERE A.ID = B.TableAID
             ORDER BY B.RowDate DESC) as B

#3

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

#4

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

#5

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 RowDate in Table B. A safer and more efficient way would be to utilize ROW_NUMBER():

只是为了清晰起见，也为了造福那些偶然发现这个古老问题的人们。如果表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

#6

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

#1

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