sql - 将多个表中的最新引用合并到共享行中的查询

Question

我有以下非常简单直接的架构：

我想编写一个优化查询，返回所有手机的列表，其中包含最新消息和拍摄的最新照片。在这种情况下，两个字段都有最新的“CreatedAt”：

预期数据集示例：

-------------------------------------------
|  Phone Id   |  Message Id |  Picture Id |
-------------------------------------------
|  1          |      3      |      4      |
|  2          |      4      |      5      |
|  3          |      5      |      6      |
-------------------------------------------

现在我不确定如何编写这样的查询，所以我只是抓取所有内容，然后使用服务器端代码以编程方式将其过滤掉，即：

SELECT * FROM Phones
LEFT OUTER JOIN Messages ON Messages.PhoneId = Phones.Id
LEFT OUTER JOIN Photos ON Photos.PhoneId = Phones.Id

--and then code that filters the CreatedAt in another language

如何编写以下查询？

标签： sqlsql-server

Answer 1

OUTER APPLY在这里似乎很有用：

SELECT p.*, m.*, ph.*
FROM Phones p OUTER APPLY
     (SELECT TOP (1) m.*
      FROM Messages m
      WHERE m.PhoneId = p.Id
      ORDER BY m.CreatedAt DESC
     ) m OUTER APPLY
     (SELECT TOP (1) ph.*
      FROM Photos ph
      WHERE ph.PhoneId = p.Id
      ORDER BY ph.CreatedAt DESC
     ) ph;