WITH cte_parent(OrderOpenId, SharedOpenId, SubjectId) AS (
SELECT
OrderOpenId,
SharedOpenId,
SubjectId
FROM
FriendsSharedInfo with(nolock)
WHERE
OrderOpenId = 'wx520c15f888888CCCSSSS' and SubjectId = 12
UNION ALL
SELECT
a.OrderOpenId,
a.SharedOpenId,
a.SubjectId
FROM
FriendsSharedInfo a with(nolock)
INNER JOIN cte_parent b ON a.OrderOpenId = b.SharedOpenId and a.SubjectId = b.SubjectId and a.OrderOpenId != ''
)
select top 1 OrderOpenId from cte_parent with(nolock) OPTION (MAXRECURSION 0) where SharedOpenId = '';
转载于:https://my.oschina.net/shma1664/blog/1476818