表A 一对多关联 表B
将关联表B中的多个字段拼接为一个字段,与表A呈现出一对一效果,查询显示
SELECT
*FROM
(
SELECT
a.id,
a.Dates,
d.PostionName,
row_number () OVER (ORDER BY a.Dates DESC) AS rowNum
FROM
A a
LEFT JOIN (
SELECT
Id,
STUFF(t.PostionName, 1, 1, '') AS PostionName
FROM
A sa CROSS APPLY (
SELECT
',' + b.Name
FROM
B b
WHERE
b.TaskId = sa.id FOR XML PATH ('')
) AS t (PostionName)
) d ON d.id = a.id
) p
WHERE
p.rowNum > 0
AND p.rowNum <= 10
ORDER BY
p.Modify DESC