Sql 如何创建一个递归计数器来查找一个元素有多少父级和子级

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```

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)

Sql相关问答推荐

无法找到正确的SQL查询需求

提高写密集型表的查询性能

基于多列比较连接两个表

使用Kotlin Exposed SQL DSL Select 多个值并排序

找到最新的连线

数据库SQL PARSE_SYNTAX_ERROR

如何简化此PostgreSQL查询以计算平均值？

如何在Postgres中为单值输入多行？

通过UPDATE SELECT更新表时出现问题

SQL OR子句如何在JOINON查询中工作？

每个分组最多 Select 最后 2 个值并并排显示它们

根据标识符将两行合并为一行

我们可以使用连接改进包含多个子查询的查询吗

Oracle PL/SQL长期运行问题

使用给定的变量对在循环中执行更新语句

Snowflake 中的对象是如何比较的？

如果 SQL 中不存在数据，如何根据某个 ID 为所有日期添加前一行

如何在 PL/SQL 中区分返回的 XML 值？

查找距上一条记录大于或等于 30 天的记录

每组跨行曲折？