我需要一个sql查询,它应该返回主表项和它的子表项(最新的一个条目仅)。我使用内连接。但是它不能正常工作。谁能给我一个合适的查询这个
Thanks in advance
在SQLServer2005+中使用OUTER APPLY操作符
SELECT *
FROM master t1 OUTER APPLY (
SELECT TOP 1 t2.Col1, t2.Col2 ...
FROM child t2
WHERE t1.Id = t2.Id
ORDER BY t2.CreatedDate DESC
) o
带有CTE和ROW_NUMBER()排序函数的OR选项
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY t1.Id ORDER BY t2.CreatedDate DESC) AS rn
FROM master t1 JOIN child t2 ON t1.Id = t2.Id
)
SELECT *
FROM cte
WHERE rn = 1
试试这个,
SELECT ID, DATE
(
SELECT M.ID, C.DATE, ROW_NUMBER() OVER(PARTITION BY M.ID ORDER BY C.DATE DESC) RN
FROM MASTER M
JOIN CHILD C
ON C.ID = M.ID
) A
WHERE RN = 1