通用表表达式问题

Question

为了计算聚类系数，我创建了以下查询：

--calculate embeddedness and local_gatekepping_role_count (for two-way connections)
    declare @observedUser varchar(50)
    declare @clusteringCoefficient decimal(18, 7)
    declare @possibleConnectionsCount int
    declare @neighborConnectionsCount int
    declare @rowsAffected int
    SET @rowsAffected = 1;
    WHILE @rowsAffected > 0
    BEGIN
    SELECT TOP 1 @observedUser = msisdn FROM users_in_group_91 WHERE clustering_coefficient IS NULL
    SET @rowsAffected = @@ROWCOUNT
    IF @rowsAffected = 0 break;
    ;WITH neighbors AS
    (
    SELECT called_party AS neighbor FROM connections_in_group_91 WHERE calling_party = @observedUser
    UNION
    SELECT calling_party AS neighbor FROM connections_in_group_91 WHERE called_party = @observedUser
    ),
    possibleConnections AS
    (
    SELECT n1.neighbor AS user1, n2.neighbor AS user2 FROM neighbors n1 CROSS JOIN neighbors n2 WHERE n1.neighbor < n2.neighbor
    )
    SELECT @clusteringCoefficient = CAST(COUNT(*) AS decimal)/(SELECT COUNT(*) FROM possibleConnections) FROM possibleConnections pc INNER JOIN connections_in_group_91 c91 ON c91.calling_party = pc.user1 AND c91.called_party = pc.user2
    )
    UPDATE users_in_group_91 SET clustering_coefficient = @clusteringCoefficient WHERE msisdn = @observedUser
    END

效果很好。因为这对检查观察到的用户扮演网守角色的次数我尝试按以下方式修改上面的查询：

declare @observedUser varchar(50)
    declare @clusteringCoefficient decimal(18, 7)
    declare @gatekeepingRoleCount int
    declare @possibleConnectionsCount int
    declare @neighborConnectionsCount int
    declare @rowsAffected int
    SET @rowsAffected = 1;
    WHILE @rowsAffected > 0
    BEGIN
    SELECT TOP 1 @observedUser = msisdn FROM users_in_group_91 WHERE clustering_coefficient IS NULL
    SET @rowsAffected = @@ROWCOUNT
    IF @rowsAffected = 0 break;
    ;WITH neighbors AS
    (
    SELECT called_party AS neighbor FROM connections_in_group_91 WHERE calling_party = @observedUser
    UNION
    SELECT calling_party AS neighbor FROM connections_in_group_91 WHERE called_party = @observedUser
    ),
    possibleConnections AS
    (
    SELECT n1.neighbor AS user1, n2.neighbor AS user2 FROM neighbors n1 CROSS JOIN neighbors n2 WHERE n1.neighbor < n2.neighbor
    )
    SELECT @possibleConnectionsCount = COUNT(*) FROM possibleConnections
    SELECT @clusteringCoefficient = CAST(COUNT(*) AS decimal)/(@possibleConnectionsCount) FROM possibleConnections pc INNER JOIN connections_in_group_91 c91 ON c91.calling_party = pc.user1 AND c91.called_party = pc.user2
    SET @gatekeepingRoleCount = @possibleConnectionsCount * (1 - @clusteringCoefficient)
    UPDATE users_in_group_91 SET clustering_coefficient = @clusteringCoefficient, local_gatekeeping_role_count = @gatekeepingRoleCount WHERE msisdn = @observedUser
    END

但后一个查询返回错误：Msg 208，Level 16，State 1，Line 25 无效的对象名称“possibleConnections”。

有谁知道为什么不能以这种方式多次查询相同的表达式以及如何修改上面的查询以便在同一循环中计算@clusteringCoefficient和@gatekeepingRoleCount？

谢谢你！

Answer 1

CTE 仅适用于以下陈述。

在您的情况下，如果您摆脱标量变量并仅将子查询内联，那么它应该可以工作。