Да можно, используя рекурсивную конструкцию WITH для T-SQL.
Вот моя табличка:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](30) NOT NULL,
[Referal] [int] NULL,
[Payment] [real] NULL
) ON [PRIMARY]
GO
Соответственно:
ID - ID текущего пользователя.
Referal - ID пригласившего, если пригласившего нет, то NULL.
Вот такой запрос для каждого ID выводит количество рефералов:
WITH T1 AS
(
SELECT ID, Name, Referal, 0 AS distance, ID as TopParent
FROM dbo.Table_3
UNION ALL
SELECT m.ID, m.Name, m.Referal, s.distance + 1 AS distance, S.TopParent
FROM T1 AS S
JOIN dbo.Table_3 AS M
ON M.Referal = S.ID
),
T2 as
(select distinct TopParent as ID, count(ID)-1 as Members
from T1
group by TopParent )
select * from T2 order by ID