I have a CTE as below (logic removed)
;with cte_a as
(
select ID, Count(AnotherID) as Counter
from Table_A
group by ID
)
and a user defined function that takes an Id as input and returns a table.
udf_GetRelatedItemIds(@Id)
I wanted to just count the number of related item ids returned from the user defined function for each ID in cte_a.
I was trying something like below but it didn't work
;with cte_a as
(
select ID, Coun开发者_运维问答t(AnotherID) as Counter
from Table_A
group by ID
)
select
ID,
Count(select RelatedId from udf_GetRelatedItemIds(ID))
from cte_a
Please suggest a solution.
What about
with cte_a as
(
select ID, Count(AnotherID) as Counter
from Table_A
group by ID
)
select
a.ID,
(SELECT COUNT(*) FROM udf_GetRelatedItemIds(a.ID)) as IntersectCount
from cte_a a
SELECT
T.ID,
(SELECT COUNT(RelatedId) FROM udf_GetRelatedItemIds(T.ID)) AS cnt
FROM
Table_A T
GROUP BY
T.ID
WARNING!!! The performance here is likely to be horrendous as it needs to run that function once for every row in Table_A. If you can recreate your function logic in a view that gives you results for all of the IDs you'd probably be better off.
精彩评论