sql - 当没有行匹配时从左表中选择最后一条记录

Question

我很难弄清楚这一点，我有 2 个表，如果两个表中没有行匹配，我想从左表的最后一行中选择数据。例如:-

表 1:-

Id     Category
1       Fruits
2       Electronics

表 2:-

Id   Item         CategoryId
1    Television       2
2    Camera           2
3    Apple            1
4    foo              3

预期输出:

Television      Electronics
Camera          Electronics
Apple           Fruits
foo             Electronics  #No Id match so last record from 1st table

如果我做 left join，它会给我 null 所以想知道在 case 语句中我是否应该再次加载第一个表的数据是这样的:-

SELECT B.Item
     , CASE WHEN B.Id IS NULL (--Take max and select table2 again?)
       ELSE B.Category END
FROM Table2 A
LEFT JOIN
    Table1 B
         ON A.CategoryId= BId

Answer 1

另一个选项是 Coalesce() 与 CROSS Join 相结合

示例

Select A.Item 
      ,Category = coalesce(B.Category,C.Category)
 From  T2 A
 Left Join  T1 B on A.CategoryId= B.Id
 Cross Join (Select Top 1 * From T1 Order by ID desc) C

返回

Item        Category
Television  Electronics
Camera      Electronics
Apple       Fruits
foo         Electronics

EDIT - Requested Update

Select A.FoodID
      ,A.BarID
       A.Item 
      ,Category = coalesce(B.Category,C.Category)
 From  @T2 A
 Left Join  @T1 B on A.CategoryId= B.Id
 Cross Apply (
              Select Top 1 Category 
               From  T1 
               Where FoodID = A.FoodID    -- Notice the WHERE
                and  BarID  = A.BarID
                Order by ID Desc
            ) C