我有两个表，一个存储一般信息，另一个保存注释。
结构是表一中的一条记录在表二中可以有许多相应的结果。但是，我正在尝试编写一个查询，该查询从表 1 中提取所有内容，并且仅从表 2 中提取最后一条注释。
例如，假设表一中有一个用户，他们在表二中有 5 条评论，我想要的查询将从表一中提取此人，而从表二中仅提取最后一条评论。
我在下面有这个查询，但是这将从表一中提取所有内容，这就是我想要的，但如果表一中的记录有多个记录，它会将它们全部带进来。
快速示例
Query results
ID Field two Field three Field four field out of table 2
1 Data Data Data Comment 1
2 Data Data Data Comment 1
2 Data Data Data Comment 2
3 Data Data Data Comment 1
4 Data Data Data Comment 1
5 Data Data Data Comment 1
如您所见，ID 为 2 的记录有两个注释，它被拉入两次，我想要的只是要显示的最后一条评论：
SELECT Qualitycontrol.ID, Qualitycontrol.MachineNo, Qualitycontrol.Description,
Qualitycontrol.CreatedTime, Qualitycontrol.ProductCode, Qualitycontrol.WorkOrder,
Qualitycontrol.Quantity, QualityControl_Comments.Comment
FROM Qualitycontrol
LEFT OUTER JOIN QualityControl_Comments
ON Qualitycontrol.ID = QualityControl_Comments.QCUID
WHERE (Qualitycontrol.CreatedTime
BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)))
ORDER BY Qualitycontrol.MachineNo
执行OUTER APPLY而不是LEFT JOIN：
SELECT Qualitycontrol.ID, Qualitycontrol.MachineNo, Qualitycontrol.Description,
Qualitycontrol.CreatedTime, Qualitycontrol.ProductCode, Qualitycontrol.WorkOrder,
Qualitycontrol.Quantity, Qc.Comment
FROM Qualitycontrol
OUTER APPLY (
SELECT TOP 5 Comment
FROM QualityControl_Comments
WHERE Qualitycontrol.ID = QCUID
ORDER BY ... DESC -- whatever defines order in QualityControl_Comments
) AS Qc(Comment)
WHERE (Qualitycontrol.CreatedTime BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)))
ORDER BY Qualitycontrol.MachineNo
您可以执行JOIN并根据MAX(CommentId)获取每条记录的最后一个注释。添加到您的WHERE子句中的这样的东西应该可以工作：
AND qcc.ID = (SELECT MAX(ID) FROM QualityControl_Comments WHERE QCID = qc.ID)
完整查询（带别名）：
SELECT qc.ID ,
qc.MachineNo ,
qc.Description ,
qc.CreatedTime ,
qc.ProductCode ,
qc.WorkOrder ,
qc.Quantity ,
qcc.Comment
FROM Qualitycontrol qc
LEFT JOIN QualityControl_Comments qcc ON qc.ID = qcc.QCUID
WHERE ( qc.CreatedTime BETWEEN CAST(GETDATE() AS DATE)
AND DATEADD(DAY, 1,
CAST(GETDATE() AS DATE)) )
AND qcc.ID = (SELECT MAX(ID) FROM QualityControl_Comments WHERE QCID = qc.ID)
ORDER BY qc.MachineNo