sql - Calculate count after recursive CTE in ms sql
问题描述
With the help of recursive CTE
I got my results as below
Group_ID Parent_ID AliasName
3 2 DEFAULT
4 2 TEMPLATE_WIN
7 2 @@APPLY_TEMPLATE@@
8 2 WIN_TEST
9 2 WIN
12 2 CONNIMPORT
13 2 INHERITANCE1
14 2 *_ENGLISH_*
10 9 WIN_LEN
11 9 WIN_1
5 4 TEMP1_WIN
6 4 TEMP2_WIN
15 3 TEST
Now I want to count number of child dependent on their parents like
WIN_LEN
and WIN_1
comes under WIN
so My expected output look like:
Group_ID Parent_ID AliasName Count
3 2 DEFAULT 1
4 2 TEMPLATE_WIN 2
7 2 @@APPLY_TEMPLATE@@ 0
8 2 WIN_TEST 0
9 2 WIN 2
12 2 CONNIMPORT 0
13 2 INHERITANCE1 0
14 2 *_ENGLISH_* 0
10 9 WIN_LEN 0
11 9 WIN_1 0
5 4 TEMP1_WIN 0
6 4 TEMP2_WIN 0
15 3 TEST 0
How to achieve the count part:
My recursive CTE
query is :
;WITH cte AS(
SELECT Group_ID,Parent_ID,AliasName
FROM clientinfo
WHERE Parent_ID = 2
UNION ALL
SELECT t.Group_ID,t.Parent_ID,t.AliasName
FROM clientinfo t INNER JOIN
cte r ON t.Parent_ID = r.Group_ID
)
select * from cte
解决方案
Seems like you could achieve this with a subquery. I've personally performed this in the FROM
using APPLY
, but you could do this in the SELECT
. Note that 'DEFAULT'
has a value of 0
for [Count]
, as 'TEST'
is not in your sample data:
--; is a statement TERMINATOR not "beginningator"
WITH VTE AS
(SELECT V.Group_ID,
V.Parent_ID,
RTRIM(V.AliasName) AS AliasName
FROM (VALUES (3, 2, 'DEFAULT '),
(4, 2, 'TEMPLATE_WIN '),
(7, 2, '@@APPLY_TEMPLATE@@'),
(8, 2, 'WIN_TEST '),
(9, 2, 'WIN '),
(12,2, 'CONNIMPORT '),
(13,2, 'INHERITANCE1 '),
(14,2, '*_ENGLISH_* '),
(10,9, 'WIN_LEN '),
(11,9, 'WIN_1 '),
(5, 4, 'TEMP1_WIN '),
(6, 4, 'TEMP2_WIN '),
(15,3, 'TEST ')) V (Group_ID, Parent_ID, AliasName) ),
rCTE AS
(SELECT Group_ID,
Parent_ID,
AliasName
FROM VTE
WHERE Parent_ID = 2
UNION ALL
SELECT t.Group_ID,
t.Parent_ID,
t.AliasName
FROM VTE t
INNER JOIN rCTE r ON t.Parent_ID = r.Group_ID)
SELECT r.Group_ID,
r.Parent_ID,
r.AliasName,
C.[Count]
FROM rCTE r
CROSS APPLY (SELECT COUNT(*) AS [Count]
FROM VTE V
WHERE V.Parent_ID = r.Group_ID) C;
'TEST'
has been added to the sample data.
推荐阅读
- salt-stack - saltstack 模式匹配失败
- android - 程序类型已存在:com.google.android.gms.internal.zzak
- c# - 命名空间不能直接包含字段或
- php - 循环重写json
- python - Python从列表中删除特定的相邻重复项
- ubuntu - ubuntu服务器上的Laravel内存不足错误
- ibm-datapower - 如何找到数据电源盒名称?是否有任何服务/全局变量保存它?
- reactjs - 我应该让所有类变量成为状态的一部分吗?
- java - 清除本地 maven 存储库后的问题
- java - 使用 application.properties 文件为 Spring Boot 测试激活配置文件