How to create a recursive counter to find out how many parents and children have an element?

I have 2 tables.
Table with reletions and table with deals.
In my table of deals I have a list of DealID's and for them I have to find out how many parents or children they have

The scripts are here:
https://dbfiddle.uk/ZM9fuv7C

Ultimately I have to get this result

WITH rec4 (ParentID, ChildID)
AS 
(
    SELECT ParentID, ChildID
    FROM t_parent 
 -- where ChildID = 106
    UNION ALL
    SELECT p.ParentID, p.ChildID
    FROM t_parent AS p
        JOIN rec4 AS r ON p.ParentID = r.ChildID

), rec5 (ParentID, ChildID)
AS 
(
    SELECT ParentID, ChildID
    FROM t_parent 
 -- where ChildID = 106
    UNION ALL
    SELECT p.ParentID, p.ChildID
    FROM t_parent AS p
        JOIN rec5 AS r ON p.ChildID = r.ParentID
)
--select ISNULL(a.DealID, 0), ttt.ParentCNT, ttt.ChildCNT
  --from #A a left join
  select ch.ParentID, ChildCNT, ParentCNT from (
select ParentID, COUNT(r5.ChildID) as ChildCNT from rec5 r5 Group by ParentID) ch
join (select ChildID, COUNT(r4.ParentID) as ParentCNT from rec4 r4 Group by ChildID) pr
on ch.ParentID = pr.ChildID--) ttt
--on a.DealID = ttt.ParentID```


</details>


# 答案1
**得分**: 1

使用两个不同的递归公共表达式（一个用于子项，另一个用于父项）似乎是一个相关的方法。然后我们可以将它们进行 `full join`，并进行聚合和计数：

```sql
with 
    children as (
        -- 省略部分代码
    ),
    parents as (
        -- 省略部分代码
    )
select coalesce(c.dealid, p.dealid) as dealid,
    count(distinct p.parentid) as parent_cnt,
    count(distinct c.childid)  as child_cnt
from children c
full join parents p on p.dealid = c.dealid
group by coalesce(c.dealid, p.dealid)

with 
    children as (
        select parentid as dealid, parentid, childid from t_parent
        union all
        select r.dealid, p.parentid, p.childid
        from children r
        inner join t_parent p on p.parentid = r.childid
    ),
    parents as (
        select childid as dealid, parentid, childid from t_parent
        union all
        select r.dealid, p.parentid, p.childid
        from parents r
        inner join t_parent p on p.childid = r.parentid
    )
select coalesce(c.dealid, p.dealid) as dealid,
    count(distinct p.parentid) as parent_cnt,
    count(distinct c.childid)  as child_cnt
from children c
full join parents p on p.dealid = c.dealid
group by coalesce(c.dealid, p.dealid)