如何使用相同的t-sql查找每个父节点的子节点数？ [英] How to find the count of child nodes per parent node using same t-sql?

问题描述

我有以下结果集：

I have following resultset:

Parent	Child 	Childcount
1	1	2
1	2	
2	1	3
2	2	
2	3	
.		
.

通过使用groupby子句，我可以在同一查询中获取父子，但不能获取子计数？

我可以使用单独的查询获取父级的子计数，但我希望在单个查询中获得整个集合

我如何实现相同的目标？到目前为止，我已经尝试了以下查询：

By using groupby clause I am able to fetch Parent Child but not the child count in the same query?
I can get child count for a parent using separate query, but I want to get the entire set in single query
How can I achieve the same? So far, I have tried following query:

SELECT parent.name AS [Parent], child.name as [Child], count (row_number() OVER (order by parent.name)) as childcount
FROM table1 parent
INNER JOIN child ON parent.id = child.id
group BY parent.name, child.name
ORDER BY parent.name

此查询引发了我的错误。任何人都可以向我提供帮助吗？谢谢。 ：）

This query throws me the error.Can anyone please provide me with the help? Thanks. :)

推荐答案

我已经在子表上使用cte和inner join解决了这个问题，如下所示：

I have solved the issue using cte and inner joining on the child table as under:

with cte1(colname1,colid1) As 
(
SELECT child.name as [Child], child.ID as [ID}
FROM table1 parent
INNER JOIN child ON parent.id = child.id
group BY parent.name, child.name
ORDER BY parent.name
)
,cte (tablename,colname,colid)as (
SELECT parent.name AS [Parent], child.name as [Child],child.ID as [ID}
FROM table parent
INNER JOIN child ON parent.id = child.id
group BY parent.name, child.name
ORDER BY parent.name
)
select DISTINCT cte.tablename, cte.colname, count(cte.colid) from cte
inner join cte1 on cte.colid = cte1.colid1
group by tablename,colname
ORDER by cte.tablename

谢谢所有人.....：）

Thanks all..... :)

在这里你会找到一个例子：具有公用表格表达式的层次结构 [ ^ ]

Here you'll find an example: Hierarchies WITH Common Table Expressions[^]

这篇关于如何使用相同的t-sql查找每个父节点的子节点数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！