Inner或其他？

Question

我所拥有的

Table A                                 Table B


ID    ID_Fruit    ,,, so on             ID      ID_Fruit   ID_CRATE  DateTime                
1     1002                              i_1     1002          1      2014-07-28 00:00.000
2     4534                              i_2     1002          2      2014-06-25 00:00.000
3     1243                              i_3     1243          NULL   2014-07-09 00:00.000
4     1003                              i_4     4534          1      2014-07-23 00:00.000

我想要的结果

ID    ID_Fruit    ,,, so on             ID      ID_Fruit    ID_CRATE     DateTime
1     1002                              i_1     1002           2      2014-06-25 00:00.000
2     4534                              i_4     4534           1      2014-07-23 00:00.000

我正在尝试的是，

SELECT * FROM TABLEA
OUTER APPLY  
        (SELECT * FROM TableB WHERE TableB.ID_Fruit = TableA.ID_Fruit AND TableB.ID_CRATE IS NOT NULL) As Table_B

我想要几行表格B中仅有的1行，这是基于壁橱DateTime

Answer 1

写为

;with CTE as 
( Select ID,
         ID_Fruit,
         ID_CRATE,
         [DateTime],
         Row_number() over ( partition by ID_Fruit order by [DateTime]  desc) as rownum
         from Table_B
 ) 
 select A.ID,A.ID_Fruit,C.ID,C.ID_CRATE,C.[DateTime] 
 from CTE C
 join Table_A A on A.ID_Fruit = C.ID_Fruit
 where C.rownum = 1 and isnull(ID_CRATE,'') <> ''

Check Demo here..

Answer 2

SELECT A.*, B.* FROM TABLE_A A, TABLE_B B
WHERE A.ID_Fruit = B.ID_Fruit
and B.DateTime IN (SELECT MAX(DateTime) from TABLE_B WHERE ID_Fruit = B.ID_Fruit)

Answer 3

SELECT
    TableAID,
    TableAID_Fruit,
    TableBID,
    TableBID_Fruit,
    ID_CRATE,
    DateTime
FROM
    (SELECT 
        TableA.ID TableAID,
        TableA.ID_Fruit TableAID_Fruit,
        TableB.ID TableBID,
        TableB.ID_Fruit TableBID_Fruit,
        TableB.ID_CRATE,
        TableB.DateTime,
        ROW_NUMBER() OVER (PARTITION BY TableB.ID_Fruit ORDER BY TableB.DateTime DESC) TableBOrder
    FROM
        TableA
        INNER JOIN TableB ON TableA.ID_Fruit = TableB.ID_Fruit
    ) Results
WHERE
    TableBOrder = 1