tsql 不同计数子查询2

Question

我正在使用 SSMS 2008，我需要使用子查询来返回唯一记录/客户端的计数。我该怎么做？目前我收到错误：

Msg 512, Level 16, State 1, Line 58
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or 

这是我目前的伪代码：

    SELECT A.Program, A.PEOPLE_ID, K.EVENT_NAME, A.Program2, A.Program3
     (SELECT COUNT(DISTINCT K.EVENT_NAME) 
     FROM #TEMP1 A, evolv_cs.dbo.facility_view F, evolv_cs.dbo.people_x N, event_view K WITH (NOLOCK)
     WHERE F.group_profile_id = A.group_profile_id AND 
        K.event_definition_id = a.event_definition_id AND
        A.people_id = N.people_id
     GROUP BY K.EVENT_NAME) as DistinctEvent
     FROM #TEMP1 A
     JOIN event_view K WITH (NOLOCK) on K.event_definition_id = A.event_definition_id
     WHERE @START_DATE BETWEEN A.Enrolled_Date AND DATEADD(D, 14, A.Enrolled_Date)
     AND (@SERVICE IS NULL OR @SERVICE = K.event_name)
 GROUP BY 
A.Program, A.PEOPLE_ID, K.EVENT_NAME, A.Program2, A.Program3

Answer 1

这应该可以更有效地工作和运行。

SELECT A.Program, A.PEOPLE_ID, sub.EVENT_NAME, A.Program2, A.Program3, sub.DistinctEvent 
FROM (
    SELECT K.EVENT_NAME, COUNT(DISTINCT K.EVENT_NAME) as DistinctEvent 
    FROM #TEMP1 as A 
    JOIN evolv_cs.dbo.facility_view as F ON F.group_profile_id = A.group_profile_id 
    JOIN evolv_cs.dbo.people_x as N ON A.people_id = N.people_id 
    JOIN event_view as K WITH (NOLOCK) ON K.event_definition_id = a.event_definition_id 
    WHERE @START_DATE BETWEEN A.Enrolled_Date AND DATEADD(D, 14, A.Enrolled_Date) 
    AND (@SERVICE IS NULL OR @SERVICE = K.event_name) 
    GROUP BY K.EVENT_NAME
) as sub 
JOIN #TEMP1 as A ON A.EVENT_NAME = sub.EVENT_NAME